1"""
   2## Expressions
   3
   4Every AST node in SQLGlot is represented by a subclass of `Expression`.
   5
   6This module contains the implementation of all supported `Expression` types. Additionally,
   7it exposes a number of helper functions, which are mainly used to programmatically build
   8SQL expressions, such as `sqlglot.expressions.select`.
   9
  10----
  11"""
  12
  13from __future__ import annotations
  14
  15import datetime
  16import math
  17import numbers
  18import re
  19import typing as t
  20from collections import deque
  21from copy import deepcopy
  22from enum import auto
  23
  24from sqlglot.errors import ParseError
  25from sqlglot.helper import (
  26    AutoName,
  27    camel_to_snake_case,
  28    ensure_collection,
  29    seq_get,
  30    split_num_words,
  31    subclasses,
  32)
  33from sqlglot.tokens import Token
  34
  35if t.TYPE_CHECKING:
  36    from sqlglot.dialects.dialect import DialectType
  37
  38E = t.TypeVar("E", bound="Expression")
  39
  40
  41class _Expression(type):
  42    def __new__(cls, clsname, bases, attrs):
  43        klass = super().__new__(cls, clsname, bases, attrs)
  44
  45        # When an Expression class is created, its key is automatically set to be
  46        # the lowercase version of the class' name.
  47        klass.key = clsname.lower()
  48
  49        # This is so that docstrings are not inherited in pdoc
  50        klass.__doc__ = klass.__doc__ or ""
  51
  52        return klass
  53
  54
  55class Expression(metaclass=_Expression):
  56    """
  57    The base class for all expressions in a syntax tree. Each Expression encapsulates any necessary
  58    context, such as its child expressions, their names (arg keys), and whether a given child expression
  59    is optional or not.
  60
  61    Attributes:
  62        key: a unique key for each class in the Expression hierarchy. This is useful for hashing
  63            and representing expressions as strings.
  64        arg_types: determines what arguments (child nodes) are supported by an expression. It
  65            maps arg keys to booleans that indicate whether the corresponding args are optional.
  66
  67    Example:
  68        >>> class Foo(Expression):
  69        ...     arg_types = {"this": True, "expression": False}
  70
  71        The above definition informs us that Foo is an Expression that requires an argument called
  72        "this" and may also optionally receive an argument called "expression".
  73
  74    Args:
  75        args: a mapping used for retrieving the arguments of an expression, given their arg keys.
  76        parent: a reference to the parent expression (or None, in case of root expressions).
  77        arg_key: the arg key an expression is associated with, i.e. the name its parent expression
  78            uses to refer to it.
  79        comments: a list of comments that are associated with a given expression. This is used in
  80            order to preserve comments when transpiling SQL code.
  81        _type: the `sqlglot.expressions.DataType` type of an expression. This is inferred by the
  82            optimizer, in order to enable some transformations that require type information.
  83    """
  84
  85    key = "expression"
  86    arg_types = {"this": True}
  87    __slots__ = ("args", "parent", "arg_key", "comments", "_type", "_meta")
  88
  89    def __init__(self, **args: t.Any):
  90        self.args: t.Dict[str, t.Any] = args
  91        self.parent: t.Optional[Expression] = None
  92        self.arg_key: t.Optional[str] = None
  93        self.comments: t.Optional[t.List[str]] = None
  94        self._type: t.Optional[DataType] = None
  95        self._meta: t.Optional[t.Dict[str, t.Any]] = None
  96
  97        for arg_key, value in self.args.items():
  98            self._set_parent(arg_key, value)
  99
 100    def __eq__(self, other) -> bool:
 101        return type(self) is type(other) and _norm_args(self) == _norm_args(other)
 102
 103    def __hash__(self) -> int:
 104        return hash(
 105            (
 106                self.key,
 107                tuple(
 108                    (k, tuple(v) if isinstance(v, list) else v) for k, v in _norm_args(self).items()
 109                ),
 110            )
 111        )
 112
 113    @property
 114    def this(self):
 115        """
 116        Retrieves the argument with key "this".
 117        """
 118        return self.args.get("this")
 119
 120    @property
 121    def expression(self):
 122        """
 123        Retrieves the argument with key "expression".
 124        """
 125        return self.args.get("expression")
 126
 127    @property
 128    def expressions(self):
 129        """
 130        Retrieves the argument with key "expressions".
 131        """
 132        return self.args.get("expressions") or []
 133
 134    def text(self, key) -> str:
 135        """
 136        Returns a textual representation of the argument corresponding to "key". This can only be used
 137        for args that are strings or leaf Expression instances, such as identifiers and literals.
 138        """
 139        field = self.args.get(key)
 140        if isinstance(field, str):
 141            return field
 142        if isinstance(field, (Identifier, Literal, Var)):
 143            return field.this
 144        if isinstance(field, (Star, Null)):
 145            return field.name
 146        return ""
 147
 148    @property
 149    def is_string(self) -> bool:
 150        """
 151        Checks whether a Literal expression is a string.
 152        """
 153        return isinstance(self, Literal) and self.args["is_string"]
 154
 155    @property
 156    def is_number(self) -> bool:
 157        """
 158        Checks whether a Literal expression is a number.
 159        """
 160        return isinstance(self, Literal) and not self.args["is_string"]
 161
 162    @property
 163    def is_int(self) -> bool:
 164        """
 165        Checks whether a Literal expression is an integer.
 166        """
 167        if self.is_number:
 168            try:
 169                int(self.name)
 170                return True
 171            except ValueError:
 172                pass
 173        return False
 174
 175    @property
 176    def is_star(self) -> bool:
 177        """Checks whether an expression is a star."""
 178        return isinstance(self, Star) or (isinstance(self, Column) and isinstance(self.this, Star))
 179
 180    @property
 181    def alias(self) -> str:
 182        """
 183        Returns the alias of the expression, or an empty string if it's not aliased.
 184        """
 185        if isinstance(self.args.get("alias"), TableAlias):
 186            return self.args["alias"].name
 187        return self.text("alias")
 188
 189    @property
 190    def name(self) -> str:
 191        return self.text("this")
 192
 193    @property
 194    def alias_or_name(self):
 195        return self.alias or self.name
 196
 197    @property
 198    def output_name(self):
 199        """
 200        Name of the output column if this expression is a selection.
 201
 202        If the Expression has no output name, an empty string is returned.
 203
 204        Example:
 205            >>> from sqlglot import parse_one
 206            >>> parse_one("SELECT a").expressions[0].output_name
 207            'a'
 208            >>> parse_one("SELECT b AS c").expressions[0].output_name
 209            'c'
 210            >>> parse_one("SELECT 1 + 2").expressions[0].output_name
 211            ''
 212        """
 213        return ""
 214
 215    @property
 216    def type(self) -> t.Optional[DataType]:
 217        return self._type
 218
 219    @type.setter
 220    def type(self, dtype: t.Optional[DataType | DataType.Type | str]) -> None:
 221        if dtype and not isinstance(dtype, DataType):
 222            dtype = DataType.build(dtype)
 223        self._type = dtype  # type: ignore
 224
 225    @property
 226    def meta(self) -> t.Dict[str, t.Any]:
 227        if self._meta is None:
 228            self._meta = {}
 229        return self._meta
 230
 231    def __deepcopy__(self, memo):
 232        copy = self.__class__(**deepcopy(self.args))
 233        if self.comments is not None:
 234            copy.comments = deepcopy(self.comments)
 235
 236        if self._type is not None:
 237            copy._type = self._type.copy()
 238
 239        if self._meta is not None:
 240            copy._meta = deepcopy(self._meta)
 241
 242        return copy
 243
 244    def copy(self):
 245        """
 246        Returns a deep copy of the expression.
 247        """
 248        new = deepcopy(self)
 249        new.parent = self.parent
 250        for item, parent, _ in new.bfs():
 251            if isinstance(item, Expression) and parent:
 252                item.parent = parent
 253        return new
 254
 255    def append(self, arg_key, value):
 256        """
 257        Appends value to arg_key if it's a list or sets it as a new list.
 258
 259        Args:
 260            arg_key (str): name of the list expression arg
 261            value (Any): value to append to the list
 262        """
 263        if not isinstance(self.args.get(arg_key), list):
 264            self.args[arg_key] = []
 265        self.args[arg_key].append(value)
 266        self._set_parent(arg_key, value)
 267
 268    def set(self, arg_key, value):
 269        """
 270        Sets `arg_key` to `value`.
 271
 272        Args:
 273            arg_key (str): name of the expression arg.
 274            value: value to set the arg to.
 275        """
 276        self.args[arg_key] = value
 277        self._set_parent(arg_key, value)
 278
 279    def _set_parent(self, arg_key, value):
 280        if isinstance(value, Expression):
 281            value.parent = self
 282            value.arg_key = arg_key
 283        elif isinstance(value, list):
 284            for v in value:
 285                if isinstance(v, Expression):
 286                    v.parent = self
 287                    v.arg_key = arg_key
 288
 289    @property
 290    def depth(self):
 291        """
 292        Returns the depth of this tree.
 293        """
 294        if self.parent:
 295            return self.parent.depth + 1
 296        return 0
 297
 298    def find(self, *expression_types: t.Type[E], bfs=True) -> E | None:
 299        """
 300        Returns the first node in this tree which matches at least one of
 301        the specified types.
 302
 303        Args:
 304            expression_types: the expression type(s) to match.
 305
 306        Returns:
 307            The node which matches the criteria or None if no such node was found.
 308        """
 309        return next(self.find_all(*expression_types, bfs=bfs), None)
 310
 311    def find_all(self, *expression_types: t.Type[E], bfs=True) -> t.Iterator[E]:
 312        """
 313        Returns a generator object which visits all nodes in this tree and only
 314        yields those that match at least one of the specified expression types.
 315
 316        Args:
 317            expression_types: the expression type(s) to match.
 318
 319        Returns:
 320            The generator object.
 321        """
 322        for expression, _, _ in self.walk(bfs=bfs):
 323            if isinstance(expression, expression_types):
 324                yield expression
 325
 326    def find_ancestor(self, *expression_types: t.Type[E]) -> E | None:
 327        """
 328        Returns a nearest parent matching expression_types.
 329
 330        Args:
 331            expression_types: the expression type(s) to match.
 332
 333        Returns:
 334            The parent node.
 335        """
 336        ancestor = self.parent
 337        while ancestor and not isinstance(ancestor, expression_types):
 338            ancestor = ancestor.parent
 339        return t.cast(E, ancestor)
 340
 341    @property
 342    def parent_select(self):
 343        """
 344        Returns the parent select statement.
 345        """
 346        return self.find_ancestor(Select)
 347
 348    def root(self) -> Expression:
 349        """
 350        Returns the root expression of this tree.
 351        """
 352        expression = self
 353        while expression.parent:
 354            expression = expression.parent
 355        return expression
 356
 357    def walk(self, bfs=True, prune=None):
 358        """
 359        Returns a generator object which visits all nodes in this tree.
 360
 361        Args:
 362            bfs (bool): if set to True the BFS traversal order will be applied,
 363                otherwise the DFS traversal will be used instead.
 364            prune ((node, parent, arg_key) -> bool): callable that returns True if
 365                the generator should stop traversing this branch of the tree.
 366
 367        Returns:
 368            the generator object.
 369        """
 370        if bfs:
 371            yield from self.bfs(prune=prune)
 372        else:
 373            yield from self.dfs(prune=prune)
 374
 375    def dfs(self, parent=None, key=None, prune=None):
 376        """
 377        Returns a generator object which visits all nodes in this tree in
 378        the DFS (Depth-first) order.
 379
 380        Returns:
 381            The generator object.
 382        """
 383        parent = parent or self.parent
 384        yield self, parent, key
 385        if prune and prune(self, parent, key):
 386            return
 387
 388        for k, v in self.args.items():
 389            for node in ensure_collection(v):
 390                if isinstance(node, Expression):
 391                    yield from node.dfs(self, k, prune)
 392
 393    def bfs(self, prune=None):
 394        """
 395        Returns a generator object which visits all nodes in this tree in
 396        the BFS (Breadth-first) order.
 397
 398        Returns:
 399            The generator object.
 400        """
 401        queue = deque([(self, self.parent, None)])
 402
 403        while queue:
 404            item, parent, key = queue.popleft()
 405
 406            yield item, parent, key
 407            if prune and prune(item, parent, key):
 408                continue
 409
 410            if isinstance(item, Expression):
 411                for k, v in item.args.items():
 412                    for node in ensure_collection(v):
 413                        if isinstance(node, Expression):
 414                            queue.append((node, item, k))
 415
 416    def unnest(self):
 417        """
 418        Returns the first non parenthesis child or self.
 419        """
 420        expression = self
 421        while isinstance(expression, Paren):
 422            expression = expression.this
 423        return expression
 424
 425    def unalias(self):
 426        """
 427        Returns the inner expression if this is an Alias.
 428        """
 429        if isinstance(self, Alias):
 430            return self.this
 431        return self
 432
 433    def unnest_operands(self):
 434        """
 435        Returns unnested operands as a tuple.
 436        """
 437        return tuple(arg.unnest() for arg in self.args.values() if arg)
 438
 439    def flatten(self, unnest=True):
 440        """
 441        Returns a generator which yields child nodes who's parents are the same class.
 442
 443        A AND B AND C -> [A, B, C]
 444        """
 445        for node, _, _ in self.dfs(prune=lambda n, p, *_: p and not isinstance(n, self.__class__)):
 446            if not isinstance(node, self.__class__):
 447                yield node.unnest() if unnest else node
 448
 449    def __str__(self):
 450        return self.sql()
 451
 452    def __repr__(self):
 453        return self._to_s()
 454
 455    def sql(self, dialect: DialectType = None, **opts) -> str:
 456        """
 457        Returns SQL string representation of this tree.
 458
 459        Args:
 460            dialect: the dialect of the output SQL string (eg. "spark", "hive", "presto", "mysql").
 461            opts: other `sqlglot.generator.Generator` options.
 462
 463        Returns:
 464            The SQL string.
 465        """
 466        from sqlglot.dialects import Dialect
 467
 468        return Dialect.get_or_raise(dialect)().generate(self, **opts)
 469
 470    def _to_s(self, hide_missing: bool = True, level: int = 0) -> str:
 471        indent = "" if not level else "\n"
 472        indent += "".join(["  "] * level)
 473        left = f"({self.key.upper()} "
 474
 475        args: t.Dict[str, t.Any] = {
 476            k: ", ".join(
 477                v._to_s(hide_missing=hide_missing, level=level + 1)
 478                if hasattr(v, "_to_s")
 479                else str(v)
 480                for v in ensure_collection(vs)
 481                if v is not None
 482            )
 483            for k, vs in self.args.items()
 484        }
 485        args["comments"] = self.comments
 486        args["type"] = self.type
 487        args = {k: v for k, v in args.items() if v or not hide_missing}
 488
 489        right = ", ".join(f"{k}: {v}" for k, v in args.items())
 490        right += ")"
 491
 492        return indent + left + right
 493
 494    def transform(self, fun, *args, copy=True, **kwargs):
 495        """
 496        Recursively visits all tree nodes (excluding already transformed ones)
 497        and applies the given transformation function to each node.
 498
 499        Args:
 500            fun (function): a function which takes a node as an argument and returns a
 501                new transformed node or the same node without modifications. If the function
 502                returns None, then the corresponding node will be removed from the syntax tree.
 503            copy (bool): if set to True a new tree instance is constructed, otherwise the tree is
 504                modified in place.
 505
 506        Returns:
 507            The transformed tree.
 508        """
 509        node = self.copy() if copy else self
 510        new_node = fun(node, *args, **kwargs)
 511
 512        if new_node is None or not isinstance(new_node, Expression):
 513            return new_node
 514        if new_node is not node:
 515            new_node.parent = node.parent
 516            return new_node
 517
 518        replace_children(new_node, lambda child: child.transform(fun, *args, copy=False, **kwargs))
 519        return new_node
 520
 521    def replace(self, expression):
 522        """
 523        Swap out this expression with a new expression.
 524
 525        For example::
 526
 527            >>> tree = Select().select("x").from_("tbl")
 528            >>> tree.find(Column).replace(Column(this="y"))
 529            (COLUMN this: y)
 530            >>> tree.sql()
 531            'SELECT y FROM tbl'
 532
 533        Args:
 534            expression (Expression|None): new node
 535
 536        Returns:
 537            The new expression or expressions.
 538        """
 539        if not self.parent:
 540            return expression
 541
 542        parent = self.parent
 543        self.parent = None
 544
 545        replace_children(parent, lambda child: expression if child is self else child)
 546        return expression
 547
 548    def pop(self):
 549        """
 550        Remove this expression from its AST.
 551
 552        Returns:
 553            The popped expression.
 554        """
 555        self.replace(None)
 556        return self
 557
 558    def assert_is(self, type_):
 559        """
 560        Assert that this `Expression` is an instance of `type_`.
 561
 562        If it is NOT an instance of `type_`, this raises an assertion error.
 563        Otherwise, this returns this expression.
 564
 565        Examples:
 566            This is useful for type security in chained expressions:
 567
 568            >>> import sqlglot
 569            >>> sqlglot.parse_one("SELECT x from y").assert_is(Select).select("z").sql()
 570            'SELECT x, z FROM y'
 571        """
 572        assert isinstance(self, type_)
 573        return self
 574
 575    def error_messages(self, args: t.Optional[t.Sequence] = None) -> t.List[str]:
 576        """
 577        Checks if this expression is valid (e.g. all mandatory args are set).
 578
 579        Args:
 580            args: a sequence of values that were used to instantiate a Func expression. This is used
 581                to check that the provided arguments don't exceed the function argument limit.
 582
 583        Returns:
 584            A list of error messages for all possible errors that were found.
 585        """
 586        errors: t.List[str] = []
 587
 588        for k in self.args:
 589            if k not in self.arg_types:
 590                errors.append(f"Unexpected keyword: '{k}' for {self.__class__}")
 591        for k, mandatory in self.arg_types.items():
 592            v = self.args.get(k)
 593            if mandatory and (v is None or (isinstance(v, list) and not v)):
 594                errors.append(f"Required keyword: '{k}' missing for {self.__class__}")
 595
 596        if (
 597            args
 598            and isinstance(self, Func)
 599            and len(args) > len(self.arg_types)
 600            and not self.is_var_len_args
 601        ):
 602            errors.append(
 603                f"The number of provided arguments ({len(args)}) is greater than "
 604                f"the maximum number of supported arguments ({len(self.arg_types)})"
 605            )
 606
 607        return errors
 608
 609    def dump(self):
 610        """
 611        Dump this Expression to a JSON-serializable dict.
 612        """
 613        from sqlglot.serde import dump
 614
 615        return dump(self)
 616
 617    @classmethod
 618    def load(cls, obj):
 619        """
 620        Load a dict (as returned by `Expression.dump`) into an Expression instance.
 621        """
 622        from sqlglot.serde import load
 623
 624        return load(obj)
 625
 626
 627IntoType = t.Union[
 628    str,
 629    t.Type[Expression],
 630    t.Collection[t.Union[str, t.Type[Expression]]],
 631]
 632ExpOrStr = t.Union[str, Expression]
 633
 634
 635class Condition(Expression):
 636    def and_(self, *expressions, dialect=None, **opts):
 637        """
 638        AND this condition with one or multiple expressions.
 639
 640        Example:
 641            >>> condition("x=1").and_("y=1").sql()
 642            'x = 1 AND y = 1'
 643
 644        Args:
 645            *expressions (str | Expression): the SQL code strings to parse.
 646                If an `Expression` instance is passed, it will be used as-is.
 647            dialect (str): the dialect used to parse the input expression.
 648            opts (kwargs): other options to use to parse the input expressions.
 649
 650        Returns:
 651            And: the new condition.
 652        """
 653        return and_(self, *expressions, dialect=dialect, **opts)
 654
 655    def or_(self, *expressions, dialect=None, **opts):
 656        """
 657        OR this condition with one or multiple expressions.
 658
 659        Example:
 660            >>> condition("x=1").or_("y=1").sql()
 661            'x = 1 OR y = 1'
 662
 663        Args:
 664            *expressions (str | Expression): the SQL code strings to parse.
 665                If an `Expression` instance is passed, it will be used as-is.
 666            dialect (str): the dialect used to parse the input expression.
 667            opts (kwargs): other options to use to parse the input expressions.
 668
 669        Returns:
 670            Or: the new condition.
 671        """
 672        return or_(self, *expressions, dialect=dialect, **opts)
 673
 674    def not_(self):
 675        """
 676        Wrap this condition with NOT.
 677
 678        Example:
 679            >>> condition("x=1").not_().sql()
 680            'NOT x = 1'
 681
 682        Returns:
 683            Not: the new condition.
 684        """
 685        return not_(self)
 686
 687
 688class Predicate(Condition):
 689    """Relationships like x = y, x > 1, x >= y."""
 690
 691
 692class DerivedTable(Expression):
 693    @property
 694    def alias_column_names(self):
 695        table_alias = self.args.get("alias")
 696        if not table_alias:
 697            return []
 698        column_list = table_alias.assert_is(TableAlias).args.get("columns") or []
 699        return [c.name for c in column_list]
 700
 701    @property
 702    def selects(self):
 703        alias = self.args.get("alias")
 704
 705        if alias:
 706            return alias.columns
 707        return []
 708
 709    @property
 710    def named_selects(self):
 711        return [select.output_name for select in self.selects]
 712
 713
 714class Unionable(Expression):
 715    def union(self, expression, distinct=True, dialect=None, **opts):
 716        """
 717        Builds a UNION expression.
 718
 719        Example:
 720            >>> import sqlglot
 721            >>> sqlglot.parse_one("SELECT * FROM foo").union("SELECT * FROM bla").sql()
 722            'SELECT * FROM foo UNION SELECT * FROM bla'
 723
 724        Args:
 725            expression (str | Expression): the SQL code string.
 726                If an `Expression` instance is passed, it will be used as-is.
 727            distinct (bool): set the DISTINCT flag if and only if this is true.
 728            dialect (str): the dialect used to parse the input expression.
 729            opts (kwargs): other options to use to parse the input expressions.
 730        Returns:
 731            Union: the Union expression.
 732        """
 733        return union(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 734
 735    def intersect(self, expression, distinct=True, dialect=None, **opts):
 736        """
 737        Builds an INTERSECT expression.
 738
 739        Example:
 740            >>> import sqlglot
 741            >>> sqlglot.parse_one("SELECT * FROM foo").intersect("SELECT * FROM bla").sql()
 742            'SELECT * FROM foo INTERSECT SELECT * FROM bla'
 743
 744        Args:
 745            expression (str | Expression): the SQL code string.
 746                If an `Expression` instance is passed, it will be used as-is.
 747            distinct (bool): set the DISTINCT flag if and only if this is true.
 748            dialect (str): the dialect used to parse the input expression.
 749            opts (kwargs): other options to use to parse the input expressions.
 750        Returns:
 751            Intersect: the Intersect expression
 752        """
 753        return intersect(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 754
 755    def except_(self, expression, distinct=True, dialect=None, **opts):
 756        """
 757        Builds an EXCEPT expression.
 758
 759        Example:
 760            >>> import sqlglot
 761            >>> sqlglot.parse_one("SELECT * FROM foo").except_("SELECT * FROM bla").sql()
 762            'SELECT * FROM foo EXCEPT SELECT * FROM bla'
 763
 764        Args:
 765            expression (str | Expression): the SQL code string.
 766                If an `Expression` instance is passed, it will be used as-is.
 767            distinct (bool): set the DISTINCT flag if and only if this is true.
 768            dialect (str): the dialect used to parse the input expression.
 769            opts (kwargs): other options to use to parse the input expressions.
 770        Returns:
 771            Except: the Except expression
 772        """
 773        return except_(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 774
 775
 776class UDTF(DerivedTable, Unionable):
 777    pass
 778
 779
 780class Cache(Expression):
 781    arg_types = {
 782        "with": False,
 783        "this": True,
 784        "lazy": False,
 785        "options": False,
 786        "expression": False,
 787    }
 788
 789
 790class Uncache(Expression):
 791    arg_types = {"this": True, "exists": False}
 792
 793
 794class Create(Expression):
 795    arg_types = {
 796        "with": False,
 797        "this": True,
 798        "kind": True,
 799        "expression": False,
 800        "exists": False,
 801        "properties": False,
 802        "replace": False,
 803        "unique": False,
 804        "volatile": False,
 805        "indexes": False,
 806        "no_schema_binding": False,
 807        "begin": False,
 808    }
 809
 810
 811class Describe(Expression):
 812    arg_types = {"this": True, "kind": False}
 813
 814
 815class Set(Expression):
 816    arg_types = {"expressions": False}
 817
 818
 819class SetItem(Expression):
 820    arg_types = {
 821        "this": False,
 822        "expressions": False,
 823        "kind": False,
 824        "collate": False,  # MySQL SET NAMES statement
 825        "global": False,
 826    }
 827
 828
 829class Show(Expression):
 830    arg_types = {
 831        "this": True,
 832        "target": False,
 833        "offset": False,
 834        "limit": False,
 835        "like": False,
 836        "where": False,
 837        "db": False,
 838        "full": False,
 839        "mutex": False,
 840        "query": False,
 841        "channel": False,
 842        "global": False,
 843        "log": False,
 844        "position": False,
 845        "types": False,
 846    }
 847
 848
 849class UserDefinedFunction(Expression):
 850    arg_types = {"this": True, "expressions": False, "wrapped": False}
 851
 852
 853class CharacterSet(Expression):
 854    arg_types = {"this": True, "default": False}
 855
 856
 857class With(Expression):
 858    arg_types = {"expressions": True, "recursive": False}
 859
 860    @property
 861    def recursive(self) -> bool:
 862        return bool(self.args.get("recursive"))
 863
 864
 865class WithinGroup(Expression):
 866    arg_types = {"this": True, "expression": False}
 867
 868
 869class CTE(DerivedTable):
 870    arg_types = {"this": True, "alias": True}
 871
 872
 873class TableAlias(Expression):
 874    arg_types = {"this": False, "columns": False}
 875
 876    @property
 877    def columns(self):
 878        return self.args.get("columns") or []
 879
 880
 881class BitString(Condition):
 882    pass
 883
 884
 885class HexString(Condition):
 886    pass
 887
 888
 889class ByteString(Condition):
 890    pass
 891
 892
 893class Column(Condition):
 894    arg_types = {"this": True, "table": False, "db": False, "catalog": False, "join_mark": False}
 895
 896    @property
 897    def table(self) -> str:
 898        return self.text("table")
 899
 900    @property
 901    def db(self) -> str:
 902        return self.text("db")
 903
 904    @property
 905    def catalog(self) -> str:
 906        return self.text("catalog")
 907
 908    @property
 909    def output_name(self) -> str:
 910        return self.name
 911
 912    @property
 913    def parts(self) -> t.List[Identifier]:
 914        """Return the parts of a column in order catalog, db, table, name."""
 915        return [part for part in reversed(list(self.args.values())) if part]
 916
 917    def to_dot(self) -> Dot:
 918        """Converts the column into a dot expression."""
 919        parts = self.parts
 920        parent = self.parent
 921
 922        while parent:
 923            if isinstance(parent, Dot):
 924                parts.append(parent.expression)
 925            parent = parent.parent
 926
 927        return Dot.build(parts)
 928
 929
 930class ColumnDef(Expression):
 931    arg_types = {
 932        "this": True,
 933        "kind": False,
 934        "constraints": False,
 935        "exists": False,
 936    }
 937
 938
 939class AlterColumn(Expression):
 940    arg_types = {
 941        "this": True,
 942        "dtype": False,
 943        "collate": False,
 944        "using": False,
 945        "default": False,
 946        "drop": False,
 947    }
 948
 949
 950class RenameTable(Expression):
 951    pass
 952
 953
 954class SetTag(Expression):
 955    arg_types = {"expressions": True, "unset": False}
 956
 957
 958class Comment(Expression):
 959    arg_types = {"this": True, "kind": True, "expression": True, "exists": False}
 960
 961
 962class ColumnConstraint(Expression):
 963    arg_types = {"this": False, "kind": True}
 964
 965
 966class ColumnConstraintKind(Expression):
 967    pass
 968
 969
 970class AutoIncrementColumnConstraint(ColumnConstraintKind):
 971    pass
 972
 973
 974class CaseSpecificColumnConstraint(ColumnConstraintKind):
 975    arg_types = {"not_": True}
 976
 977
 978class CharacterSetColumnConstraint(ColumnConstraintKind):
 979    arg_types = {"this": True}
 980
 981
 982class CheckColumnConstraint(ColumnConstraintKind):
 983    pass
 984
 985
 986class CollateColumnConstraint(ColumnConstraintKind):
 987    pass
 988
 989
 990class CommentColumnConstraint(ColumnConstraintKind):
 991    pass
 992
 993
 994class CompressColumnConstraint(ColumnConstraintKind):
 995    pass
 996
 997
 998class DateFormatColumnConstraint(ColumnConstraintKind):
 999    arg_types = {"this": True}
1000
1001
1002class DefaultColumnConstraint(ColumnConstraintKind):
1003    pass
1004
1005
1006class EncodeColumnConstraint(ColumnConstraintKind):
1007    pass
1008
1009
1010class GeneratedAsIdentityColumnConstraint(ColumnConstraintKind):
1011    # this: True -> ALWAYS, this: False -> BY DEFAULT
1012    arg_types = {
1013        "this": False,
1014        "start": False,
1015        "increment": False,
1016        "minvalue": False,
1017        "maxvalue": False,
1018        "cycle": False,
1019    }
1020
1021
1022class InlineLengthColumnConstraint(ColumnConstraintKind):
1023    pass
1024
1025
1026class NotNullColumnConstraint(ColumnConstraintKind):
1027    arg_types = {"allow_null": False}
1028
1029
1030class PrimaryKeyColumnConstraint(ColumnConstraintKind):
1031    arg_types = {"desc": False}
1032
1033
1034class TitleColumnConstraint(ColumnConstraintKind):
1035    pass
1036
1037
1038class UniqueColumnConstraint(ColumnConstraintKind):
1039    arg_types: t.Dict[str, t.Any] = {}
1040
1041
1042class UppercaseColumnConstraint(ColumnConstraintKind):
1043    arg_types: t.Dict[str, t.Any] = {}
1044
1045
1046class PathColumnConstraint(ColumnConstraintKind):
1047    pass
1048
1049
1050class Constraint(Expression):
1051    arg_types = {"this": True, "expressions": True}
1052
1053
1054class Delete(Expression):
1055    arg_types = {"with": False, "this": False, "using": False, "where": False, "returning": False}
1056
1057    def delete(
1058        self,
1059        table: ExpOrStr,
1060        dialect: DialectType = None,
1061        copy: bool = True,
1062        **opts,
1063    ) -> Delete:
1064        """
1065        Create a DELETE expression or replace the table on an existing DELETE expression.
1066
1067        Example:
1068            >>> delete("tbl").sql()
1069            'DELETE FROM tbl'
1070
1071        Args:
1072            table: the table from which to delete.
1073            dialect: the dialect used to parse the input expression.
1074            copy: if `False`, modify this expression instance in-place.
1075            opts: other options to use to parse the input expressions.
1076
1077        Returns:
1078            Delete: the modified expression.
1079        """
1080        return _apply_builder(
1081            expression=table,
1082            instance=self,
1083            arg="this",
1084            dialect=dialect,
1085            into=Table,
1086            copy=copy,
1087            **opts,
1088        )
1089
1090    def where(
1091        self,
1092        *expressions: ExpOrStr,
1093        append: bool = True,
1094        dialect: DialectType = None,
1095        copy: bool = True,
1096        **opts,
1097    ) -> Delete:
1098        """
1099        Append to or set the WHERE expressions.
1100
1101        Example:
1102            >>> delete("tbl").where("x = 'a' OR x < 'b'").sql()
1103            "DELETE FROM tbl WHERE x = 'a' OR x < 'b'"
1104
1105        Args:
1106            *expressions: the SQL code strings to parse.
1107                If an `Expression` instance is passed, it will be used as-is.
1108                Multiple expressions are combined with an AND operator.
1109            append: if `True`, AND the new expressions to any existing expression.
1110                Otherwise, this resets the expression.
1111            dialect: the dialect used to parse the input expressions.
1112            copy: if `False`, modify this expression instance in-place.
1113            opts: other options to use to parse the input expressions.
1114
1115        Returns:
1116            Delete: the modified expression.
1117        """
1118        return _apply_conjunction_builder(
1119            *expressions,
1120            instance=self,
1121            arg="where",
1122            append=append,
1123            into=Where,
1124            dialect=dialect,
1125            copy=copy,
1126            **opts,
1127        )
1128
1129    def returning(
1130        self,
1131        expression: ExpOrStr,
1132        dialect: DialectType = None,
1133        copy: bool = True,
1134        **opts,
1135    ) -> Delete:
1136        """
1137        Set the RETURNING expression. Not supported by all dialects.
1138
1139        Example:
1140            >>> delete("tbl").returning("*", dialect="postgres").sql()
1141            'DELETE FROM tbl RETURNING *'
1142
1143        Args:
1144            expression: the SQL code strings to parse.
1145                If an `Expression` instance is passed, it will be used as-is.
1146            dialect: the dialect used to parse the input expressions.
1147            copy: if `False`, modify this expression instance in-place.
1148            opts: other options to use to parse the input expressions.
1149
1150        Returns:
1151            Delete: the modified expression.
1152        """
1153        return _apply_builder(
1154            expression=expression,
1155            instance=self,
1156            arg="returning",
1157            prefix="RETURNING",
1158            dialect=dialect,
1159            copy=copy,
1160            into=Returning,
1161            **opts,
1162        )
1163
1164
1165class Drop(Expression):
1166    arg_types = {
1167        "this": False,
1168        "kind": False,
1169        "exists": False,
1170        "temporary": False,
1171        "materialized": False,
1172        "cascade": False,
1173    }
1174
1175
1176class Filter(Expression):
1177    arg_types = {"this": True, "expression": True}
1178
1179
1180class Check(Expression):
1181    pass
1182
1183
1184class Directory(Expression):
1185    # https://spark.apache.org/docs/3.0.0-preview/sql-ref-syntax-dml-insert-overwrite-directory-hive.html
1186    arg_types = {"this": True, "local": False, "row_format": False}
1187
1188
1189class ForeignKey(Expression):
1190    arg_types = {
1191        "expressions": True,
1192        "reference": False,
1193        "delete": False,
1194        "update": False,
1195    }
1196
1197
1198class PrimaryKey(Expression):
1199    arg_types = {"expressions": True, "options": False}
1200
1201
1202class Unique(Expression):
1203    arg_types = {"expressions": True}
1204
1205
1206# https://www.postgresql.org/docs/9.1/sql-selectinto.html
1207# https://docs.aws.amazon.com/redshift/latest/dg/r_SELECT_INTO.html#r_SELECT_INTO-examples
1208class Into(Expression):
1209    arg_types = {"this": True, "temporary": False, "unlogged": False}
1210
1211
1212class From(Expression):
1213    arg_types = {"expressions": True}
1214
1215
1216class Having(Expression):
1217    pass
1218
1219
1220class Hint(Expression):
1221    arg_types = {"expressions": True}
1222
1223
1224class JoinHint(Expression):
1225    arg_types = {"this": True, "expressions": True}
1226
1227
1228class Identifier(Expression):
1229    arg_types = {"this": True, "quoted": False}
1230
1231    @property
1232    def quoted(self):
1233        return bool(self.args.get("quoted"))
1234
1235    def __eq__(self, other):
1236        return isinstance(other, self.__class__) and _norm_arg(self.this) == _norm_arg(other.this)
1237
1238    def __hash__(self):
1239        return hash((self.key, self.this.lower()))
1240
1241    @property
1242    def output_name(self):
1243        return self.name
1244
1245
1246class Index(Expression):
1247    arg_types = {
1248        "this": False,
1249        "table": False,
1250        "where": False,
1251        "columns": False,
1252        "unique": False,
1253        "primary": False,
1254        "amp": False,  # teradata
1255    }
1256
1257
1258class Insert(Expression):
1259    arg_types = {
1260        "with": False,
1261        "this": True,
1262        "expression": False,
1263        "returning": False,
1264        "overwrite": False,
1265        "exists": False,
1266        "partition": False,
1267        "alternative": False,
1268    }
1269
1270
1271class Returning(Expression):
1272    arg_types = {"expressions": True}
1273
1274
1275# https://dev.mysql.com/doc/refman/8.0/en/charset-introducer.html
1276class Introducer(Expression):
1277    arg_types = {"this": True, "expression": True}
1278
1279
1280# national char, like n'utf8'
1281class National(Expression):
1282    pass
1283
1284
1285class LoadData(Expression):
1286    arg_types = {
1287        "this": True,
1288        "local": False,
1289        "overwrite": False,
1290        "inpath": True,
1291        "partition": False,
1292        "input_format": False,
1293        "serde": False,
1294    }
1295
1296
1297class Partition(Expression):
1298    arg_types = {"expressions": True}
1299
1300
1301class Fetch(Expression):
1302    arg_types = {"direction": False, "count": False}
1303
1304
1305class Group(Expression):
1306    arg_types = {
1307        "expressions": False,
1308        "grouping_sets": False,
1309        "cube": False,
1310        "rollup": False,
1311    }
1312
1313
1314class Lambda(Expression):
1315    arg_types = {"this": True, "expressions": True}
1316
1317
1318class Limit(Expression):
1319    arg_types = {"this": False, "expression": True}
1320
1321
1322class Literal(Condition):
1323    arg_types = {"this": True, "is_string": True}
1324
1325    def __eq__(self, other):
1326        return (
1327            isinstance(other, Literal)
1328            and self.this == other.this
1329            and self.args["is_string"] == other.args["is_string"]
1330        )
1331
1332    def __hash__(self):
1333        return hash((self.key, self.this, self.args["is_string"]))
1334
1335    @classmethod
1336    def number(cls, number) -> Literal:
1337        return cls(this=str(number), is_string=False)
1338
1339    @classmethod
1340    def string(cls, string) -> Literal:
1341        return cls(this=str(string), is_string=True)
1342
1343    @property
1344    def output_name(self):
1345        return self.name
1346
1347
1348class Join(Expression):
1349    arg_types = {
1350        "this": True,
1351        "on": False,
1352        "side": False,
1353        "kind": False,
1354        "using": False,
1355        "natural": False,
1356    }
1357
1358    @property
1359    def kind(self):
1360        return self.text("kind").upper()
1361
1362    @property
1363    def side(self):
1364        return self.text("side").upper()
1365
1366    @property
1367    def alias_or_name(self):
1368        return self.this.alias_or_name
1369
1370    def on(self, *expressions, append=True, dialect=None, copy=True, **opts):
1371        """
1372        Append to or set the ON expressions.
1373
1374        Example:
1375            >>> import sqlglot
1376            >>> sqlglot.parse_one("JOIN x", into=Join).on("y = 1").sql()
1377            'JOIN x ON y = 1'
1378
1379        Args:
1380            *expressions (str | Expression): the SQL code strings to parse.
1381                If an `Expression` instance is passed, it will be used as-is.
1382                Multiple expressions are combined with an AND operator.
1383            append (bool): if `True`, AND the new expressions to any existing expression.
1384                Otherwise, this resets the expression.
1385            dialect (str): the dialect used to parse the input expressions.
1386            copy (bool): if `False`, modify this expression instance in-place.
1387            opts (kwargs): other options to use to parse the input expressions.
1388
1389        Returns:
1390            Join: the modified join expression.
1391        """
1392        join = _apply_conjunction_builder(
1393            *expressions,
1394            instance=self,
1395            arg="on",
1396            append=append,
1397            dialect=dialect,
1398            copy=copy,
1399            **opts,
1400        )
1401
1402        if join.kind == "CROSS":
1403            join.set("kind", None)
1404
1405        return join
1406
1407    def using(self, *expressions, append=True, dialect=None, copy=True, **opts):
1408        """
1409        Append to or set the USING expressions.
1410
1411        Example:
1412            >>> import sqlglot
1413            >>> sqlglot.parse_one("JOIN x", into=Join).using("foo", "bla").sql()
1414            'JOIN x USING (foo, bla)'
1415
1416        Args:
1417            *expressions (str | Expression): the SQL code strings to parse.
1418                If an `Expression` instance is passed, it will be used as-is.
1419            append (bool): if `True`, concatenate the new expressions to the existing "using" list.
1420                Otherwise, this resets the expression.
1421            dialect (str): the dialect used to parse the input expressions.
1422            copy (bool): if `False`, modify this expression instance in-place.
1423            opts (kwargs): other options to use to parse the input expressions.
1424
1425        Returns:
1426            Join: the modified join expression.
1427        """
1428        join = _apply_list_builder(
1429            *expressions,
1430            instance=self,
1431            arg="using",
1432            append=append,
1433            dialect=dialect,
1434            copy=copy,
1435            **opts,
1436        )
1437
1438        if join.kind == "CROSS":
1439            join.set("kind", None)
1440
1441        return join
1442
1443
1444class Lateral(UDTF):
1445    arg_types = {"this": True, "view": False, "outer": False, "alias": False}
1446
1447
1448class MatchRecognize(Expression):
1449    arg_types = {
1450        "partition_by": False,
1451        "order": False,
1452        "measures": False,
1453        "rows": False,
1454        "after": False,
1455        "pattern": False,
1456        "define": False,
1457    }
1458
1459
1460# Clickhouse FROM FINAL modifier
1461# https://clickhouse.com/docs/en/sql-reference/statements/select/from/#final-modifier
1462class Final(Expression):
1463    pass
1464
1465
1466class Offset(Expression):
1467    arg_types = {"this": False, "expression": True}
1468
1469
1470class Order(Expression):
1471    arg_types = {"this": False, "expressions": True}
1472
1473
1474# hive specific sorts
1475# https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SortBy
1476class Cluster(Order):
1477    pass
1478
1479
1480class Distribute(Order):
1481    pass
1482
1483
1484class Sort(Order):
1485    pass
1486
1487
1488class Ordered(Expression):
1489    arg_types = {"this": True, "desc": True, "nulls_first": True}
1490
1491
1492class Property(Expression):
1493    arg_types = {"this": True, "value": True}
1494
1495
1496class AfterJournalProperty(Property):
1497    arg_types = {"no": True, "dual": False, "local": False}
1498
1499
1500class AlgorithmProperty(Property):
1501    arg_types = {"this": True}
1502
1503
1504class AutoIncrementProperty(Property):
1505    arg_types = {"this": True}
1506
1507
1508class BlockCompressionProperty(Property):
1509    arg_types = {"autotemp": False, "always": False, "default": True, "manual": True, "never": True}
1510
1511
1512class CharacterSetProperty(Property):
1513    arg_types = {"this": True, "default": True}
1514
1515
1516class ChecksumProperty(Property):
1517    arg_types = {"on": False, "default": False}
1518
1519
1520class CollateProperty(Property):
1521    arg_types = {"this": True}
1522
1523
1524class DataBlocksizeProperty(Property):
1525    arg_types = {"size": False, "units": False, "min": False, "default": False}
1526
1527
1528class DefinerProperty(Property):
1529    arg_types = {"this": True}
1530
1531
1532class DistKeyProperty(Property):
1533    arg_types = {"this": True}
1534
1535
1536class DistStyleProperty(Property):
1537    arg_types = {"this": True}
1538
1539
1540class EngineProperty(Property):
1541    arg_types = {"this": True}
1542
1543
1544class ExecuteAsProperty(Property):
1545    arg_types = {"this": True}
1546
1547
1548class ExternalProperty(Property):
1549    arg_types = {"this": False}
1550
1551
1552class FallbackProperty(Property):
1553    arg_types = {"no": True, "protection": False}
1554
1555
1556class FileFormatProperty(Property):
1557    arg_types = {"this": True}
1558
1559
1560class FreespaceProperty(Property):
1561    arg_types = {"this": True, "percent": False}
1562
1563
1564class IsolatedLoadingProperty(Property):
1565    arg_types = {
1566        "no": True,
1567        "concurrent": True,
1568        "for_all": True,
1569        "for_insert": True,
1570        "for_none": True,
1571    }
1572
1573
1574class JournalProperty(Property):
1575    arg_types = {"no": True, "dual": False, "before": False}
1576
1577
1578class LanguageProperty(Property):
1579    arg_types = {"this": True}
1580
1581
1582class LikeProperty(Property):
1583    arg_types = {"this": True, "expressions": False}
1584
1585
1586class LocationProperty(Property):
1587    arg_types = {"this": True}
1588
1589
1590class LockingProperty(Property):
1591    arg_types = {
1592        "this": False,
1593        "kind": True,
1594        "for_or_in": True,
1595        "lock_type": True,
1596        "override": False,
1597    }
1598
1599
1600class LogProperty(Property):
1601    arg_types = {"no": True}
1602
1603
1604class MaterializedProperty(Property):
1605    arg_types = {"this": False}
1606
1607
1608class MergeBlockRatioProperty(Property):
1609    arg_types = {"this": False, "no": False, "default": False, "percent": False}
1610
1611
1612class NoPrimaryIndexProperty(Property):
1613    arg_types = {"this": False}
1614
1615
1616class OnCommitProperty(Property):
1617    arg_type = {"this": False}
1618
1619
1620class PartitionedByProperty(Property):
1621    arg_types = {"this": True}
1622
1623
1624class ReturnsProperty(Property):
1625    arg_types = {"this": True, "is_table": False, "table": False}
1626
1627
1628class RowFormatDelimitedProperty(Property):
1629    # https://cwiki.apache.org/confluence/display/hive/languagemanual+dml
1630    arg_types = {
1631        "fields": False,
1632        "escaped": False,
1633        "collection_items": False,
1634        "map_keys": False,
1635        "lines": False,
1636        "null": False,
1637        "serde": False,
1638    }
1639
1640
1641class RowFormatSerdeProperty(Property):
1642    arg_types = {"this": True}
1643
1644
1645class SchemaCommentProperty(Property):
1646    arg_types = {"this": True}
1647
1648
1649class SerdeProperties(Property):
1650    arg_types = {"expressions": True}
1651
1652
1653class SetProperty(Property):
1654    arg_types = {"multi": True}
1655
1656
1657class SortKeyProperty(Property):
1658    arg_types = {"this": True, "compound": False}
1659
1660
1661class SqlSecurityProperty(Property):
1662    arg_types = {"definer": True}
1663
1664
1665class TableFormatProperty(Property):
1666    arg_types = {"this": True}
1667
1668
1669class TemporaryProperty(Property):
1670    arg_types = {"global_": True}
1671
1672
1673class TransientProperty(Property):
1674    arg_types = {"this": False}
1675
1676
1677class VolatilityProperty(Property):
1678    arg_types = {"this": True}
1679
1680
1681class WithDataProperty(Property):
1682    arg_types = {"no": True, "statistics": False}
1683
1684
1685class WithJournalTableProperty(Property):
1686    arg_types = {"this": True}
1687
1688
1689class Properties(Expression):
1690    arg_types = {"expressions": True}
1691
1692    NAME_TO_PROPERTY = {
1693        "ALGORITHM": AlgorithmProperty,
1694        "AUTO_INCREMENT": AutoIncrementProperty,
1695        "CHARACTER SET": CharacterSetProperty,
1696        "COLLATE": CollateProperty,
1697        "COMMENT": SchemaCommentProperty,
1698        "DEFINER": DefinerProperty,
1699        "DISTKEY": DistKeyProperty,
1700        "DISTSTYLE": DistStyleProperty,
1701        "ENGINE": EngineProperty,
1702        "EXECUTE AS": ExecuteAsProperty,
1703        "FORMAT": FileFormatProperty,
1704        "LANGUAGE": LanguageProperty,
1705        "LOCATION": LocationProperty,
1706        "PARTITIONED_BY": PartitionedByProperty,
1707        "RETURNS": ReturnsProperty,
1708        "SORTKEY": SortKeyProperty,
1709        "TABLE_FORMAT": TableFormatProperty,
1710    }
1711
1712    PROPERTY_TO_NAME = {v: k for k, v in NAME_TO_PROPERTY.items()}
1713
1714    # CREATE property locations
1715    # Form: schema specified
1716    #   create [POST_CREATE]
1717    #     table a [POST_NAME]
1718    #     (b int) [POST_SCHEMA]
1719    #     with ([POST_WITH])
1720    #     index (b) [POST_INDEX]
1721    #
1722    # Form: alias selection
1723    #   create [POST_CREATE]
1724    #     table a [POST_NAME]
1725    #     as [POST_ALIAS] (select * from b) [POST_EXPRESSION]
1726    #     index (c) [POST_INDEX]
1727    class Location(AutoName):
1728        POST_CREATE = auto()
1729        POST_NAME = auto()
1730        POST_SCHEMA = auto()
1731        POST_WITH = auto()
1732        POST_ALIAS = auto()
1733        POST_EXPRESSION = auto()
1734        POST_INDEX = auto()
1735        UNSUPPORTED = auto()
1736
1737    @classmethod
1738    def from_dict(cls, properties_dict) -> Properties:
1739        expressions = []
1740        for key, value in properties_dict.items():
1741            property_cls = cls.NAME_TO_PROPERTY.get(key.upper())
1742            if property_cls:
1743                expressions.append(property_cls(this=convert(value)))
1744            else:
1745                expressions.append(Property(this=Literal.string(key), value=convert(value)))
1746
1747        return cls(expressions=expressions)
1748
1749
1750class Qualify(Expression):
1751    pass
1752
1753
1754# https://www.ibm.com/docs/en/ias?topic=procedures-return-statement-in-sql
1755class Return(Expression):
1756    pass
1757
1758
1759class Reference(Expression):
1760    arg_types = {"this": True, "expressions": False, "options": False}
1761
1762
1763class Tuple(Expression):
1764    arg_types = {"expressions": False}
1765
1766
1767class Subqueryable(Unionable):
1768    def subquery(self, alias=None, copy=True) -> Subquery:
1769        """
1770        Convert this expression to an aliased expression that can be used as a Subquery.
1771
1772        Example:
1773            >>> subquery = Select().select("x").from_("tbl").subquery()
1774            >>> Select().select("x").from_(subquery).sql()
1775            'SELECT x FROM (SELECT x FROM tbl)'
1776
1777        Args:
1778            alias (str | Identifier): an optional alias for the subquery
1779            copy (bool): if `False`, modify this expression instance in-place.
1780
1781        Returns:
1782            Alias: the subquery
1783        """
1784        instance = _maybe_copy(self, copy)
1785        return Subquery(
1786            this=instance,
1787            alias=TableAlias(this=to_identifier(alias)),
1788        )
1789
1790    def limit(self, expression, dialect=None, copy=True, **opts) -> Select:
1791        raise NotImplementedError
1792
1793    @property
1794    def ctes(self):
1795        with_ = self.args.get("with")
1796        if not with_:
1797            return []
1798        return with_.expressions
1799
1800    @property
1801    def selects(self):
1802        raise NotImplementedError("Subqueryable objects must implement `selects`")
1803
1804    @property
1805    def named_selects(self):
1806        raise NotImplementedError("Subqueryable objects must implement `named_selects`")
1807
1808    def with_(
1809        self,
1810        alias,
1811        as_,
1812        recursive=None,
1813        append=True,
1814        dialect=None,
1815        copy=True,
1816        **opts,
1817    ):
1818        """
1819        Append to or set the common table expressions.
1820
1821        Example:
1822            >>> Select().with_("tbl2", as_="SELECT * FROM tbl").select("x").from_("tbl2").sql()
1823            'WITH tbl2 AS (SELECT * FROM tbl) SELECT x FROM tbl2'
1824
1825        Args:
1826            alias (str | Expression): the SQL code string to parse as the table name.
1827                If an `Expression` instance is passed, this is used as-is.
1828            as_ (str | Expression): the SQL code string to parse as the table expression.
1829                If an `Expression` instance is passed, it will be used as-is.
1830            recursive (bool): set the RECURSIVE part of the expression. Defaults to `False`.
1831            append (bool): if `True`, add to any existing expressions.
1832                Otherwise, this resets the expressions.
1833            dialect (str): the dialect used to parse the input expression.
1834            copy (bool): if `False`, modify this expression instance in-place.
1835            opts (kwargs): other options to use to parse the input expressions.
1836
1837        Returns:
1838            Select: the modified expression.
1839        """
1840        alias_expression = maybe_parse(
1841            alias,
1842            dialect=dialect,
1843            into=TableAlias,
1844            **opts,
1845        )
1846        as_expression = maybe_parse(
1847            as_,
1848            dialect=dialect,
1849            **opts,
1850        )
1851        cte = CTE(
1852            this=as_expression,
1853            alias=alias_expression,
1854        )
1855        return _apply_child_list_builder(
1856            cte,
1857            instance=self,
1858            arg="with",
1859            append=append,
1860            copy=copy,
1861            into=With,
1862            properties={"recursive": recursive or False},
1863        )
1864
1865
1866QUERY_MODIFIERS = {
1867    "match": False,
1868    "laterals": False,
1869    "joins": False,
1870    "pivots": False,
1871    "where": False,
1872    "group": False,
1873    "having": False,
1874    "qualify": False,
1875    "windows": False,
1876    "distribute": False,
1877    "sort": False,
1878    "cluster": False,
1879    "order": False,
1880    "limit": False,
1881    "offset": False,
1882    "lock": False,
1883    "sample": False,
1884}
1885
1886
1887class Table(Expression):
1888    arg_types = {
1889        "this": True,
1890        "alias": False,
1891        "db": False,
1892        "catalog": False,
1893        "laterals": False,
1894        "joins": False,
1895        "pivots": False,
1896        "hints": False,
1897        "system_time": False,
1898    }
1899
1900    @property
1901    def db(self) -> str:
1902        return self.text("db")
1903
1904    @property
1905    def catalog(self) -> str:
1906        return self.text("catalog")
1907
1908
1909# See the TSQL "Querying data in a system-versioned temporal table" page
1910class SystemTime(Expression):
1911    arg_types = {
1912        "this": False,
1913        "expression": False,
1914        "kind": True,
1915    }
1916
1917
1918class Union(Subqueryable):
1919    arg_types = {
1920        "with": False,
1921        "this": True,
1922        "expression": True,
1923        "distinct": False,
1924        **QUERY_MODIFIERS,
1925    }
1926
1927    def limit(self, expression, dialect=None, copy=True, **opts) -> Select:
1928        """
1929        Set the LIMIT expression.
1930
1931        Example:
1932            >>> select("1").union(select("1")).limit(1).sql()
1933            'SELECT * FROM (SELECT 1 UNION SELECT 1) AS _l_0 LIMIT 1'
1934
1935        Args:
1936            expression (str | int | Expression): the SQL code string to parse.
1937                This can also be an integer.
1938                If a `Limit` instance is passed, this is used as-is.
1939                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
1940            dialect (str): the dialect used to parse the input expression.
1941            copy (bool): if `False`, modify this expression instance in-place.
1942            opts (kwargs): other options to use to parse the input expressions.
1943
1944        Returns:
1945            Select: The limited subqueryable.
1946        """
1947        return (
1948            select("*")
1949            .from_(self.subquery(alias="_l_0", copy=copy))
1950            .limit(expression, dialect=dialect, copy=False, **opts)
1951        )
1952
1953    def select(
1954        self,
1955        *expressions: ExpOrStr,
1956        append: bool = True,
1957        dialect: DialectType = None,
1958        copy: bool = True,
1959        **opts,
1960    ) -> Union:
1961        """Append to or set the SELECT of the union recursively.
1962
1963        Example:
1964            >>> from sqlglot import parse_one
1965            >>> parse_one("select a from x union select a from y union select a from z").select("b").sql()
1966            'SELECT a, b FROM x UNION SELECT a, b FROM y UNION SELECT a, b FROM z'
1967
1968        Args:
1969            *expressions: the SQL code strings to parse.
1970                If an `Expression` instance is passed, it will be used as-is.
1971            append: if `True`, add to any existing expressions.
1972                Otherwise, this resets the expressions.
1973            dialect: the dialect used to parse the input expressions.
1974            copy: if `False`, modify this expression instance in-place.
1975            opts: other options to use to parse the input expressions.
1976
1977        Returns:
1978            Union: the modified expression.
1979        """
1980        this = self.copy() if copy else self
1981        this.this.unnest().select(*expressions, append=append, dialect=dialect, copy=False, **opts)
1982        this.expression.unnest().select(
1983            *expressions, append=append, dialect=dialect, copy=False, **opts
1984        )
1985        return this
1986
1987    @property
1988    def named_selects(self):
1989        return self.this.unnest().named_selects
1990
1991    @property
1992    def is_star(self) -> bool:
1993        return self.this.is_star or self.expression.is_star
1994
1995    @property
1996    def selects(self):
1997        return self.this.unnest().selects
1998
1999    @property
2000    def left(self):
2001        return self.this
2002
2003    @property
2004    def right(self):
2005        return self.expression
2006
2007
2008class Except(Union):
2009    pass
2010
2011
2012class Intersect(Union):
2013    pass
2014
2015
2016class Unnest(UDTF):
2017    arg_types = {
2018        "expressions": True,
2019        "ordinality": False,
2020        "alias": False,
2021        "offset": False,
2022    }
2023
2024
2025class Update(Expression):
2026    arg_types = {
2027        "with": False,
2028        "this": False,
2029        "expressions": True,
2030        "from": False,
2031        "where": False,
2032        "returning": False,
2033    }
2034
2035
2036class Values(UDTF):
2037    arg_types = {
2038        "expressions": True,
2039        "ordinality": False,
2040        "alias": False,
2041    }
2042
2043
2044class Var(Expression):
2045    pass
2046
2047
2048class Schema(Expression):
2049    arg_types = {"this": False, "expressions": False}
2050
2051
2052# Used to represent the FOR UPDATE and FOR SHARE locking read types.
2053# https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-reads.html
2054class Lock(Expression):
2055    arg_types = {"update": True}
2056
2057
2058class Select(Subqueryable):
2059    arg_types = {
2060        "with": False,
2061        "expressions": False,
2062        "hint": False,
2063        "distinct": False,
2064        "into": False,
2065        "from": False,
2066        **QUERY_MODIFIERS,
2067    }
2068
2069    def from_(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2070        """
2071        Set the FROM expression.
2072
2073        Example:
2074            >>> Select().from_("tbl").select("x").sql()
2075            'SELECT x FROM tbl'
2076
2077        Args:
2078            *expressions (str | Expression): the SQL code strings to parse.
2079                If a `From` instance is passed, this is used as-is.
2080                If another `Expression` instance is passed, it will be wrapped in a `From`.
2081            append (bool): if `True`, add to any existing expressions.
2082                Otherwise, this flattens all the `From` expression into a single expression.
2083            dialect (str): the dialect used to parse the input expression.
2084            copy (bool): if `False`, modify this expression instance in-place.
2085            opts (kwargs): other options to use to parse the input expressions.
2086
2087        Returns:
2088            Select: the modified expression.
2089        """
2090        return _apply_child_list_builder(
2091            *expressions,
2092            instance=self,
2093            arg="from",
2094            append=append,
2095            copy=copy,
2096            prefix="FROM",
2097            into=From,
2098            dialect=dialect,
2099            **opts,
2100        )
2101
2102    def group_by(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2103        """
2104        Set the GROUP BY expression.
2105
2106        Example:
2107            >>> Select().from_("tbl").select("x", "COUNT(1)").group_by("x").sql()
2108            'SELECT x, COUNT(1) FROM tbl GROUP BY x'
2109
2110        Args:
2111            *expressions (str | Expression): the SQL code strings to parse.
2112                If a `Group` instance is passed, this is used as-is.
2113                If another `Expression` instance is passed, it will be wrapped in a `Group`.
2114                If nothing is passed in then a group by is not applied to the expression
2115            append (bool): if `True`, add to any existing expressions.
2116                Otherwise, this flattens all the `Group` expression into a single expression.
2117            dialect (str): the dialect used to parse the input expression.
2118            copy (bool): if `False`, modify this expression instance in-place.
2119            opts (kwargs): other options to use to parse the input expressions.
2120
2121        Returns:
2122            Select: the modified expression.
2123        """
2124        if not expressions:
2125            return self if not copy else self.copy()
2126        return _apply_child_list_builder(
2127            *expressions,
2128            instance=self,
2129            arg="group",
2130            append=append,
2131            copy=copy,
2132            prefix="GROUP BY",
2133            into=Group,
2134            dialect=dialect,
2135            **opts,
2136        )
2137
2138    def order_by(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2139        """
2140        Set the ORDER BY expression.
2141
2142        Example:
2143            >>> Select().from_("tbl").select("x").order_by("x DESC").sql()
2144            'SELECT x FROM tbl ORDER BY x DESC'
2145
2146        Args:
2147            *expressions (str | Expression): the SQL code strings to parse.
2148                If a `Group` instance is passed, this is used as-is.
2149                If another `Expression` instance is passed, it will be wrapped in a `Order`.
2150            append (bool): if `True`, add to any existing expressions.
2151                Otherwise, this flattens all the `Order` expression into a single expression.
2152            dialect (str): the dialect used to parse the input expression.
2153            copy (bool): if `False`, modify this expression instance in-place.
2154            opts (kwargs): other options to use to parse the input expressions.
2155
2156        Returns:
2157            Select: the modified expression.
2158        """
2159        return _apply_child_list_builder(
2160            *expressions,
2161            instance=self,
2162            arg="order",
2163            append=append,
2164            copy=copy,
2165            prefix="ORDER BY",
2166            into=Order,
2167            dialect=dialect,
2168            **opts,
2169        )
2170
2171    def sort_by(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2172        """
2173        Set the SORT BY expression.
2174
2175        Example:
2176            >>> Select().from_("tbl").select("x").sort_by("x DESC").sql()
2177            'SELECT x FROM tbl SORT BY x DESC'
2178
2179        Args:
2180            *expressions (str | Expression): the SQL code strings to parse.
2181                If a `Group` instance is passed, this is used as-is.
2182                If another `Expression` instance is passed, it will be wrapped in a `SORT`.
2183            append (bool): if `True`, add to any existing expressions.
2184                Otherwise, this flattens all the `Order` expression into a single expression.
2185            dialect (str): the dialect used to parse the input expression.
2186            copy (bool): if `False`, modify this expression instance in-place.
2187            opts (kwargs): other options to use to parse the input expressions.
2188
2189        Returns:
2190            Select: the modified expression.
2191        """
2192        return _apply_child_list_builder(
2193            *expressions,
2194            instance=self,
2195            arg="sort",
2196            append=append,
2197            copy=copy,
2198            prefix="SORT BY",
2199            into=Sort,
2200            dialect=dialect,
2201            **opts,
2202        )
2203
2204    def cluster_by(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2205        """
2206        Set the CLUSTER BY expression.
2207
2208        Example:
2209            >>> Select().from_("tbl").select("x").cluster_by("x DESC").sql()
2210            'SELECT x FROM tbl CLUSTER BY x DESC'
2211
2212        Args:
2213            *expressions (str | Expression): the SQL code strings to parse.
2214                If a `Group` instance is passed, this is used as-is.
2215                If another `Expression` instance is passed, it will be wrapped in a `Cluster`.
2216            append (bool): if `True`, add to any existing expressions.
2217                Otherwise, this flattens all the `Order` expression into a single expression.
2218            dialect (str): the dialect used to parse the input expression.
2219            copy (bool): if `False`, modify this expression instance in-place.
2220            opts (kwargs): other options to use to parse the input expressions.
2221
2222        Returns:
2223            Select: the modified expression.
2224        """
2225        return _apply_child_list_builder(
2226            *expressions,
2227            instance=self,
2228            arg="cluster",
2229            append=append,
2230            copy=copy,
2231            prefix="CLUSTER BY",
2232            into=Cluster,
2233            dialect=dialect,
2234            **opts,
2235        )
2236
2237    def limit(self, expression, dialect=None, copy=True, **opts) -> Select:
2238        """
2239        Set the LIMIT expression.
2240
2241        Example:
2242            >>> Select().from_("tbl").select("x").limit(10).sql()
2243            'SELECT x FROM tbl LIMIT 10'
2244
2245        Args:
2246            expression (str | int | Expression): the SQL code string to parse.
2247                This can also be an integer.
2248                If a `Limit` instance is passed, this is used as-is.
2249                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
2250            dialect (str): the dialect used to parse the input expression.
2251            copy (bool): if `False`, modify this expression instance in-place.
2252            opts (kwargs): other options to use to parse the input expressions.
2253
2254        Returns:
2255            Select: the modified expression.
2256        """
2257        return _apply_builder(
2258            expression=expression,
2259            instance=self,
2260            arg="limit",
2261            into=Limit,
2262            prefix="LIMIT",
2263            dialect=dialect,
2264            copy=copy,
2265            **opts,
2266        )
2267
2268    def offset(self, expression, dialect=None, copy=True, **opts) -> Select:
2269        """
2270        Set the OFFSET expression.
2271
2272        Example:
2273            >>> Select().from_("tbl").select("x").offset(10).sql()
2274            'SELECT x FROM tbl OFFSET 10'
2275
2276        Args:
2277            expression (str | int | Expression): the SQL code string to parse.
2278                This can also be an integer.
2279                If a `Offset` instance is passed, this is used as-is.
2280                If another `Expression` instance is passed, it will be wrapped in a `Offset`.
2281            dialect (str): the dialect used to parse the input expression.
2282            copy (bool): if `False`, modify this expression instance in-place.
2283            opts (kwargs): other options to use to parse the input expressions.
2284
2285        Returns:
2286            Select: the modified expression.
2287        """
2288        return _apply_builder(
2289            expression=expression,
2290            instance=self,
2291            arg="offset",
2292            into=Offset,
2293            prefix="OFFSET",
2294            dialect=dialect,
2295            copy=copy,
2296            **opts,
2297        )
2298
2299    def select(
2300        self,
2301        *expressions: ExpOrStr,
2302        append: bool = True,
2303        dialect: DialectType = None,
2304        copy: bool = True,
2305        **opts,
2306    ) -> Select:
2307        """
2308        Append to or set the SELECT expressions.
2309
2310        Example:
2311            >>> Select().select("x", "y").sql()
2312            'SELECT x, y'
2313
2314        Args:
2315            *expressions: the SQL code strings to parse.
2316                If an `Expression` instance is passed, it will be used as-is.
2317            append: if `True`, add to any existing expressions.
2318                Otherwise, this resets the expressions.
2319            dialect: the dialect used to parse the input expressions.
2320            copy: if `False`, modify this expression instance in-place.
2321            opts: other options to use to parse the input expressions.
2322
2323        Returns:
2324            Select: the modified expression.
2325        """
2326        return _apply_list_builder(
2327            *expressions,
2328            instance=self,
2329            arg="expressions",
2330            append=append,
2331            dialect=dialect,
2332            copy=copy,
2333            **opts,
2334        )
2335
2336    def lateral(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2337        """
2338        Append to or set the LATERAL expressions.
2339
2340        Example:
2341            >>> Select().select("x").lateral("OUTER explode(y) tbl2 AS z").from_("tbl").sql()
2342            'SELECT x FROM tbl LATERAL VIEW OUTER EXPLODE(y) tbl2 AS z'
2343
2344        Args:
2345            *expressions (str | Expression): the SQL code strings to parse.
2346                If an `Expression` instance is passed, it will be used as-is.
2347            append (bool): if `True`, add to any existing expressions.
2348                Otherwise, this resets the expressions.
2349            dialect (str): the dialect used to parse the input expressions.
2350            copy (bool): if `False`, modify this expression instance in-place.
2351            opts (kwargs): other options to use to parse the input expressions.
2352
2353        Returns:
2354            Select: the modified expression.
2355        """
2356        return _apply_list_builder(
2357            *expressions,
2358            instance=self,
2359            arg="laterals",
2360            append=append,
2361            into=Lateral,
2362            prefix="LATERAL VIEW",
2363            dialect=dialect,
2364            copy=copy,
2365            **opts,
2366        )
2367
2368    def join(
2369        self,
2370        expression,
2371        on=None,
2372        using=None,
2373        append=True,
2374        join_type=None,
2375        join_alias=None,
2376        dialect=None,
2377        copy=True,
2378        **opts,
2379    ) -> Select:
2380        """
2381        Append to or set the JOIN expressions.
2382
2383        Example:
2384            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y").sql()
2385            'SELECT * FROM tbl JOIN tbl2 ON tbl1.y = tbl2.y'
2386
2387            >>> Select().select("1").from_("a").join("b", using=["x", "y", "z"]).sql()
2388            'SELECT 1 FROM a JOIN b USING (x, y, z)'
2389
2390            Use `join_type` to change the type of join:
2391
2392            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y", join_type="left outer").sql()
2393            'SELECT * FROM tbl LEFT OUTER JOIN tbl2 ON tbl1.y = tbl2.y'
2394
2395        Args:
2396            expression (str | Expression): the SQL code string to parse.
2397                If an `Expression` instance is passed, it will be used as-is.
2398            on (str | Expression): optionally specify the join "on" criteria as a SQL string.
2399                If an `Expression` instance is passed, it will be used as-is.
2400            using (str | Expression): optionally specify the join "using" criteria as a SQL string.
2401                If an `Expression` instance is passed, it will be used as-is.
2402            append (bool): if `True`, add to any existing expressions.
2403                Otherwise, this resets the expressions.
2404            join_type (str): If set, alter the parsed join type
2405            dialect (str): the dialect used to parse the input expressions.
2406            copy (bool): if `False`, modify this expression instance in-place.
2407            opts (kwargs): other options to use to parse the input expressions.
2408
2409        Returns:
2410            Select: the modified expression.
2411        """
2412        parse_args = {"dialect": dialect, **opts}
2413
2414        try:
2415            expression = maybe_parse(expression, into=Join, prefix="JOIN", **parse_args)
2416        except ParseError:
2417            expression = maybe_parse(expression, into=(Join, Expression), **parse_args)
2418
2419        join = expression if isinstance(expression, Join) else Join(this=expression)
2420
2421        if isinstance(join.this, Select):
2422            join.this.replace(join.this.subquery())
2423
2424        if join_type:
2425            natural: t.Optional[Token]
2426            side: t.Optional[Token]
2427            kind: t.Optional[Token]
2428
2429            natural, side, kind = maybe_parse(join_type, into="JOIN_TYPE", **parse_args)  # type: ignore
2430
2431            if natural:
2432                join.set("natural", True)
2433            if side:
2434                join.set("side", side.text)
2435            if kind:
2436                join.set("kind", kind.text)
2437
2438        if on:
2439            on = and_(*ensure_collection(on), dialect=dialect, **opts)
2440            join.set("on", on)
2441
2442        if using:
2443            join = _apply_list_builder(
2444                *ensure_collection(using),
2445                instance=join,
2446                arg="using",
2447                append=append,
2448                copy=copy,
2449                **opts,
2450            )
2451
2452        if join_alias:
2453            join.set("this", alias_(join.this, join_alias, table=True))
2454        return _apply_list_builder(
2455            join,
2456            instance=self,
2457            arg="joins",
2458            append=append,
2459            copy=copy,
2460            **opts,
2461        )
2462
2463    def where(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2464        """
2465        Append to or set the WHERE expressions.
2466
2467        Example:
2468            >>> Select().select("x").from_("tbl").where("x = 'a' OR x < 'b'").sql()
2469            "SELECT x FROM tbl WHERE x = 'a' OR x < 'b'"
2470
2471        Args:
2472            *expressions (str | Expression): the SQL code strings to parse.
2473                If an `Expression` instance is passed, it will be used as-is.
2474                Multiple expressions are combined with an AND operator.
2475            append (bool): if `True`, AND the new expressions to any existing expression.
2476                Otherwise, this resets the expression.
2477            dialect (str): the dialect used to parse the input expressions.
2478            copy (bool): if `False`, modify this expression instance in-place.
2479            opts (kwargs): other options to use to parse the input expressions.
2480
2481        Returns:
2482            Select: the modified expression.
2483        """
2484        return _apply_conjunction_builder(
2485            *expressions,
2486            instance=self,
2487            arg="where",
2488            append=append,
2489            into=Where,
2490            dialect=dialect,
2491            copy=copy,
2492            **opts,
2493        )
2494
2495    def having(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2496        """
2497        Append to or set the HAVING expressions.
2498
2499        Example:
2500            >>> Select().select("x", "COUNT(y)").from_("tbl").group_by("x").having("COUNT(y) > 3").sql()
2501            'SELECT x, COUNT(y) FROM tbl GROUP BY x HAVING COUNT(y) > 3'
2502
2503        Args:
2504            *expressions (str | Expression): the SQL code strings to parse.
2505                If an `Expression` instance is passed, it will be used as-is.
2506                Multiple expressions are combined with an AND operator.
2507            append (bool): if `True`, AND the new expressions to any existing expression.
2508                Otherwise, this resets the expression.
2509            dialect (str): the dialect used to parse the input expressions.
2510            copy (bool): if `False`, modify this expression instance in-place.
2511            opts (kwargs): other options to use to parse the input expressions.
2512
2513        Returns:
2514            Select: the modified expression.
2515        """
2516        return _apply_conjunction_builder(
2517            *expressions,
2518            instance=self,
2519            arg="having",
2520            append=append,
2521            into=Having,
2522            dialect=dialect,
2523            copy=copy,
2524            **opts,
2525        )
2526
2527    def window(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2528        return _apply_list_builder(
2529            *expressions,
2530            instance=self,
2531            arg="windows",
2532            append=append,
2533            into=Window,
2534            dialect=dialect,
2535            copy=copy,
2536            **opts,
2537        )
2538
2539    def qualify(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2540        return _apply_conjunction_builder(
2541            *expressions,
2542            instance=self,
2543            arg="qualify",
2544            append=append,
2545            into=Qualify,
2546            dialect=dialect,
2547            copy=copy,
2548            **opts,
2549        )
2550
2551    def distinct(self, distinct=True, copy=True) -> Select:
2552        """
2553        Set the OFFSET expression.
2554
2555        Example:
2556            >>> Select().from_("tbl").select("x").distinct().sql()
2557            'SELECT DISTINCT x FROM tbl'
2558
2559        Args:
2560            distinct (bool): whether the Select should be distinct
2561            copy (bool): if `False`, modify this expression instance in-place.
2562
2563        Returns:
2564            Select: the modified expression.
2565        """
2566        instance = _maybe_copy(self, copy)
2567        instance.set("distinct", Distinct() if distinct else None)
2568        return instance
2569
2570    def ctas(self, table, properties=None, dialect=None, copy=True, **opts) -> Create:
2571        """
2572        Convert this expression to a CREATE TABLE AS statement.
2573
2574        Example:
2575            >>> Select().select("*").from_("tbl").ctas("x").sql()
2576            'CREATE TABLE x AS SELECT * FROM tbl'
2577
2578        Args:
2579            table (str | Expression): the SQL code string to parse as the table name.
2580                If another `Expression` instance is passed, it will be used as-is.
2581            properties (dict): an optional mapping of table properties
2582            dialect (str): the dialect used to parse the input table.
2583            copy (bool): if `False`, modify this expression instance in-place.
2584            opts (kwargs): other options to use to parse the input table.
2585
2586        Returns:
2587            Create: the CREATE TABLE AS expression
2588        """
2589        instance = _maybe_copy(self, copy)
2590        table_expression = maybe_parse(
2591            table,
2592            into=Table,
2593            dialect=dialect,
2594            **opts,
2595        )
2596        properties_expression = None
2597        if properties:
2598            properties_expression = Properties.from_dict(properties)
2599
2600        return Create(
2601            this=table_expression,
2602            kind="table",
2603            expression=instance,
2604            properties=properties_expression,
2605        )
2606
2607    def lock(self, update: bool = True, copy: bool = True) -> Select:
2608        """
2609        Set the locking read mode for this expression.
2610
2611        Examples:
2612            >>> Select().select("x").from_("tbl").where("x = 'a'").lock().sql("mysql")
2613            "SELECT x FROM tbl WHERE x = 'a' FOR UPDATE"
2614
2615            >>> Select().select("x").from_("tbl").where("x = 'a'").lock(update=False).sql("mysql")
2616            "SELECT x FROM tbl WHERE x = 'a' FOR SHARE"
2617
2618        Args:
2619            update: if `True`, the locking type will be `FOR UPDATE`, else it will be `FOR SHARE`.
2620            copy: if `False`, modify this expression instance in-place.
2621
2622        Returns:
2623            The modified expression.
2624        """
2625
2626        inst = _maybe_copy(self, copy)
2627        inst.set("lock", Lock(update=update))
2628
2629        return inst
2630
2631    @property
2632    def named_selects(self) -> t.List[str]:
2633        return [e.output_name for e in self.expressions if e.alias_or_name]
2634
2635    @property
2636    def is_star(self) -> bool:
2637        return any(expression.is_star for expression in self.expressions)
2638
2639    @property
2640    def selects(self) -> t.List[Expression]:
2641        return self.expressions
2642
2643
2644class Subquery(DerivedTable, Unionable):
2645    arg_types = {
2646        "this": True,
2647        "alias": False,
2648        "with": False,
2649        **QUERY_MODIFIERS,
2650    }
2651
2652    def unnest(self):
2653        """
2654        Returns the first non subquery.
2655        """
2656        expression = self
2657        while isinstance(expression, Subquery):
2658            expression = expression.this
2659        return expression
2660
2661    @property
2662    def is_star(self) -> bool:
2663        return self.this.is_star
2664
2665    @property
2666    def output_name(self):
2667        return self.alias
2668
2669
2670class TableSample(Expression):
2671    arg_types = {
2672        "this": False,
2673        "method": False,
2674        "bucket_numerator": False,
2675        "bucket_denominator": False,
2676        "bucket_field": False,
2677        "percent": False,
2678        "rows": False,
2679        "size": False,
2680        "seed": False,
2681        "kind": False,
2682    }
2683
2684
2685class Tag(Expression):
2686    """Tags are used for generating arbitrary sql like SELECT <span>x</span>."""
2687
2688    arg_types = {
2689        "this": False,
2690        "prefix": False,
2691        "postfix": False,
2692    }
2693
2694
2695class Pivot(Expression):
2696    arg_types = {
2697        "this": False,
2698        "alias": False,
2699        "expressions": True,
2700        "field": True,
2701        "unpivot": True,
2702    }
2703
2704
2705class Window(Expression):
2706    arg_types = {
2707        "this": True,
2708        "partition_by": False,
2709        "order": False,
2710        "spec": False,
2711        "alias": False,
2712    }
2713
2714
2715class WindowSpec(Expression):
2716    arg_types = {
2717        "kind": False,
2718        "start": False,
2719        "start_side": False,
2720        "end": False,
2721        "end_side": False,
2722    }
2723
2724
2725class Where(Expression):
2726    pass
2727
2728
2729class Star(Expression):
2730    arg_types = {"except": False, "replace": False}
2731
2732    @property
2733    def name(self) -> str:
2734        return "*"
2735
2736    @property
2737    def output_name(self):
2738        return self.name
2739
2740
2741class Parameter(Expression):
2742    arg_types = {"this": True, "wrapped": False}
2743
2744
2745class SessionParameter(Expression):
2746    arg_types = {"this": True, "kind": False}
2747
2748
2749class Placeholder(Expression):
2750    arg_types = {"this": False}
2751
2752
2753class Null(Condition):
2754    arg_types: t.Dict[str, t.Any] = {}
2755
2756    @property
2757    def name(self) -> str:
2758        return "NULL"
2759
2760
2761class Boolean(Condition):
2762    pass
2763
2764
2765class DataType(Expression):
2766    arg_types = {
2767        "this": True,
2768        "expressions": False,
2769        "nested": False,
2770        "values": False,
2771        "prefix": False,
2772    }
2773
2774    class Type(AutoName):
2775        CHAR = auto()
2776        NCHAR = auto()
2777        VARCHAR = auto()
2778        NVARCHAR = auto()
2779        TEXT = auto()
2780        MEDIUMTEXT = auto()
2781        LONGTEXT = auto()
2782        MEDIUMBLOB = auto()
2783        LONGBLOB = auto()
2784        BINARY = auto()
2785        VARBINARY = auto()
2786        INT = auto()
2787        UINT = auto()
2788        TINYINT = auto()
2789        UTINYINT = auto()
2790        SMALLINT = auto()
2791        USMALLINT = auto()
2792        BIGINT = auto()
2793        UBIGINT = auto()
2794        FLOAT = auto()
2795        DOUBLE = auto()
2796        DECIMAL = auto()
2797        BIT = auto()
2798        BOOLEAN = auto()
2799        JSON = auto()
2800        JSONB = auto()
2801        INTERVAL = auto()
2802        TIME = auto()
2803        TIMESTAMP = auto()
2804        TIMESTAMPTZ = auto()
2805        TIMESTAMPLTZ = auto()
2806        DATE = auto()
2807        DATETIME = auto()
2808        ARRAY = auto()
2809        MAP = auto()
2810        UUID = auto()
2811        GEOGRAPHY = auto()
2812        GEOMETRY = auto()
2813        STRUCT = auto()
2814        NULLABLE = auto()
2815        HLLSKETCH = auto()
2816        HSTORE = auto()
2817        SUPER = auto()
2818        SERIAL = auto()
2819        SMALLSERIAL = auto()
2820        BIGSERIAL = auto()
2821        XML = auto()
2822        UNIQUEIDENTIFIER = auto()
2823        MONEY = auto()
2824        SMALLMONEY = auto()
2825        ROWVERSION = auto()
2826        IMAGE = auto()
2827        VARIANT = auto()
2828        OBJECT = auto()
2829        INET = auto()
2830        NULL = auto()
2831        UNKNOWN = auto()  # Sentinel value, useful for type annotation
2832
2833    TEXT_TYPES = {
2834        Type.CHAR,
2835        Type.NCHAR,
2836        Type.VARCHAR,
2837        Type.NVARCHAR,
2838        Type.TEXT,
2839    }
2840
2841    INTEGER_TYPES = {
2842        Type.INT,
2843        Type.TINYINT,
2844        Type.SMALLINT,
2845        Type.BIGINT,
2846    }
2847
2848    FLOAT_TYPES = {
2849        Type.FLOAT,
2850        Type.DOUBLE,
2851    }
2852
2853    NUMERIC_TYPES = {*INTEGER_TYPES, *FLOAT_TYPES}
2854
2855    TEMPORAL_TYPES = {
2856        Type.TIMESTAMP,
2857        Type.TIMESTAMPTZ,
2858        Type.TIMESTAMPLTZ,
2859        Type.DATE,
2860        Type.DATETIME,
2861    }
2862
2863    @classmethod
2864    def build(
2865        cls, dtype: str | DataType | DataType.Type, dialect: DialectType = None, **kwargs
2866    ) -> DataType:
2867        from sqlglot import parse_one
2868
2869        if isinstance(dtype, str):
2870            if dtype.upper() in cls.Type.__members__:
2871                data_type_exp: t.Optional[Expression] = DataType(this=DataType.Type[dtype.upper()])
2872            else:
2873                data_type_exp = parse_one(dtype, read=dialect, into=DataType)
2874            if data_type_exp is None:
2875                raise ValueError(f"Unparsable data type value: {dtype}")
2876        elif isinstance(dtype, DataType.Type):
2877            data_type_exp = DataType(this=dtype)
2878        elif isinstance(dtype, DataType):
2879            return dtype
2880        else:
2881            raise ValueError(f"Invalid data type: {type(dtype)}. Expected str or DataType.Type")
2882        return DataType(**{**data_type_exp.args, **kwargs})
2883
2884    def is_type(self, dtype: DataType.Type) -> bool:
2885        return self.this == dtype
2886
2887
2888# https://www.postgresql.org/docs/15/datatype-pseudo.html
2889class PseudoType(Expression):
2890    pass
2891
2892
2893class StructKwarg(Expression):
2894    arg_types = {"this": True, "expression": True}
2895
2896
2897# WHERE x <OP> EXISTS|ALL|ANY|SOME(SELECT ...)
2898class SubqueryPredicate(Predicate):
2899    pass
2900
2901
2902class All(SubqueryPredicate):
2903    pass
2904
2905
2906class Any(SubqueryPredicate):
2907    pass
2908
2909
2910class Exists(SubqueryPredicate):
2911    pass
2912
2913
2914# Commands to interact with the databases or engines. For most of the command
2915# expressions we parse whatever comes after the command's name as a string.
2916class Command(Expression):
2917    arg_types = {"this": True, "expression": False}
2918
2919
2920class Transaction(Expression):
2921    arg_types = {"this": False, "modes": False}
2922
2923
2924class Commit(Expression):
2925    arg_types = {"chain": False}
2926
2927
2928class Rollback(Expression):
2929    arg_types = {"savepoint": False}
2930
2931
2932class AlterTable(Expression):
2933    arg_types = {"this": True, "actions": True, "exists": False}
2934
2935
2936class AddConstraint(Expression):
2937    arg_types = {"this": False, "expression": False, "enforced": False}
2938
2939
2940class DropPartition(Expression):
2941    arg_types = {"expressions": True, "exists": False}
2942
2943
2944# Binary expressions like (ADD a b)
2945class Binary(Expression):
2946    arg_types = {"this": True, "expression": True}
2947
2948    @property
2949    def left(self):
2950        return self.this
2951
2952    @property
2953    def right(self):
2954        return self.expression
2955
2956
2957class Add(Binary):
2958    pass
2959
2960
2961class Connector(Binary, Condition):
2962    pass
2963
2964
2965class And(Connector):
2966    pass
2967
2968
2969class Or(Connector):
2970    pass
2971
2972
2973class BitwiseAnd(Binary):
2974    pass
2975
2976
2977class BitwiseLeftShift(Binary):
2978    pass
2979
2980
2981class BitwiseOr(Binary):
2982    pass
2983
2984
2985class BitwiseRightShift(Binary):
2986    pass
2987
2988
2989class BitwiseXor(Binary):
2990    pass
2991
2992
2993class Div(Binary):
2994    pass
2995
2996
2997class Overlaps(Binary):
2998    pass
2999
3000
3001class Dot(Binary):
3002    @property
3003    def name(self) -> str:
3004        return self.expression.name
3005
3006    @classmethod
3007    def build(self, expressions: t.Sequence[Expression]) -> Dot:
3008        """Build a Dot object with a sequence of expressions."""
3009        if len(expressions) < 2:
3010            raise ValueError(f"Dot requires >= 2 expressions.")
3011
3012        a, b, *expressions = expressions
3013        dot = Dot(this=a, expression=b)
3014
3015        for expression in expressions:
3016            dot = Dot(this=dot, expression=expression)
3017
3018        return dot
3019
3020
3021class DPipe(Binary):
3022    pass
3023
3024
3025class EQ(Binary, Predicate):
3026    pass
3027
3028
3029class NullSafeEQ(Binary, Predicate):
3030    pass
3031
3032
3033class NullSafeNEQ(Binary, Predicate):
3034    pass
3035
3036
3037class Distance(Binary):
3038    pass
3039
3040
3041class Escape(Binary):
3042    pass
3043
3044
3045class Glob(Binary, Predicate):
3046    pass
3047
3048
3049class GT(Binary, Predicate):
3050    pass
3051
3052
3053class GTE(Binary, Predicate):
3054    pass
3055
3056
3057class ILike(Binary, Predicate):
3058    pass
3059
3060
3061class ILikeAny(Binary, Predicate):
3062    pass
3063
3064
3065class IntDiv(Binary):
3066    pass
3067
3068
3069class Is(Binary, Predicate):
3070    pass
3071
3072
3073class Kwarg(Binary):
3074    """Kwarg in special functions like func(kwarg => y)."""
3075
3076
3077class Like(Binary, Predicate):
3078    pass
3079
3080
3081class LikeAny(Binary, Predicate):
3082    pass
3083
3084
3085class LT(Binary, Predicate):
3086    pass
3087
3088
3089class LTE(Binary, Predicate):
3090    pass
3091
3092
3093class Mod(Binary):
3094    pass
3095
3096
3097class Mul(Binary):
3098    pass
3099
3100
3101class NEQ(Binary, Predicate):
3102    pass
3103
3104
3105class SimilarTo(Binary, Predicate):
3106    pass
3107
3108
3109class Slice(Binary):
3110    arg_types = {"this": False, "expression": False}
3111
3112
3113class Sub(Binary):
3114    pass
3115
3116
3117class ArrayOverlaps(Binary):
3118    pass
3119
3120
3121# Unary Expressions
3122# (NOT a)
3123class Unary(Expression):
3124    pass
3125
3126
3127class BitwiseNot(Unary):
3128    pass
3129
3130
3131class Not(Unary, Condition):
3132    pass
3133
3134
3135class Paren(Unary, Condition):
3136    arg_types = {"this": True, "with": False}
3137
3138
3139class Neg(Unary):
3140    pass
3141
3142
3143# Special Functions
3144class Alias(Expression):
3145    arg_types = {"this": True, "alias": False}
3146
3147    @property
3148    def output_name(self):
3149        return self.alias
3150
3151
3152class Aliases(Expression):
3153    arg_types = {"this": True, "expressions": True}
3154
3155    @property
3156    def aliases(self):
3157        return self.expressions
3158
3159
3160class AtTimeZone(Expression):
3161    arg_types = {"this": True, "zone": True}
3162
3163
3164class Between(Predicate):
3165    arg_types = {"this": True, "low": True, "high": True}
3166
3167
3168class Bracket(Condition):
3169    arg_types = {"this": True, "expressions": True}
3170
3171
3172class Distinct(Expression):
3173    arg_types = {"expressions": False, "on": False}
3174
3175
3176class In(Predicate):
3177    arg_types = {
3178        "this": True,
3179        "expressions": False,
3180        "query": False,
3181        "unnest": False,
3182        "field": False,
3183        "is_global": False,
3184    }
3185
3186
3187class TimeUnit(Expression):
3188    """Automatically converts unit arg into a var."""
3189
3190    arg_types = {"unit": False}
3191
3192    def __init__(self, **args):
3193        unit = args.get("unit")
3194        if isinstance(unit, (Column, Literal)):
3195            args["unit"] = Var(this=unit.name)
3196        elif isinstance(unit, Week):
3197            unit.set("this", Var(this=unit.this.name))
3198        super().__init__(**args)
3199
3200
3201class Interval(TimeUnit):
3202    arg_types = {"this": False, "unit": False}
3203
3204
3205class IgnoreNulls(Expression):
3206    pass
3207
3208
3209class RespectNulls(Expression):
3210    pass
3211
3212
3213# Functions
3214class Func(Condition):
3215    """
3216    The base class for all function expressions.
3217
3218    Attributes:
3219        is_var_len_args (bool): if set to True the last argument defined in arg_types will be
3220            treated as a variable length argument and the argument's value will be stored as a list.
3221        _sql_names (list): determines the SQL name (1st item in the list) and aliases (subsequent items)
3222            for this function expression. These values are used to map this node to a name during parsing
3223            as well as to provide the function's name during SQL string generation. By default the SQL
3224            name is set to the expression's class name transformed to snake case.
3225    """
3226
3227    is_var_len_args = False
3228
3229    @classmethod
3230    def from_arg_list(cls, args):
3231        if cls.is_var_len_args:
3232            all_arg_keys = list(cls.arg_types)
3233            # If this function supports variable length argument treat the last argument as such.
3234            non_var_len_arg_keys = all_arg_keys[:-1] if cls.is_var_len_args else all_arg_keys
3235            num_non_var = len(non_var_len_arg_keys)
3236
3237            args_dict = {arg_key: arg for arg, arg_key in zip(args, non_var_len_arg_keys)}
3238            args_dict[all_arg_keys[-1]] = args[num_non_var:]
3239        else:
3240            args_dict = {arg_key: arg for arg, arg_key in zip(args, cls.arg_types)}
3241
3242        return cls(**args_dict)
3243
3244    @classmethod
3245    def sql_names(cls):
3246        if cls is Func:
3247            raise NotImplementedError(
3248                "SQL name is only supported by concrete function implementations"
3249            )
3250        if "_sql_names" not in cls.__dict__:
3251            cls._sql_names = [camel_to_snake_case(cls.__name__)]
3252        return cls._sql_names
3253
3254    @classmethod
3255    def sql_name(cls):
3256        return cls.sql_names()[0]
3257
3258    @classmethod
3259    def default_parser_mappings(cls):
3260        return {name: cls.from_arg_list for name in cls.sql_names()}
3261
3262
3263class AggFunc(Func):
3264    pass
3265
3266
3267class Abs(Func):
3268    pass
3269
3270
3271class Anonymous(Func):
3272    arg_types = {"this": True, "expressions": False}
3273    is_var_len_args = True
3274
3275
3276class ApproxDistinct(AggFunc):
3277    arg_types = {"this": True, "accuracy": False}
3278
3279
3280class Array(Func):
3281    arg_types = {"expressions": False}
3282    is_var_len_args = True
3283
3284
3285# https://docs.snowflake.com/en/sql-reference/functions/to_char
3286class ToChar(Func):
3287    arg_types = {"this": True, "format": False}
3288
3289
3290class GenerateSeries(Func):
3291    arg_types = {"start": True, "end": True, "step": False}
3292
3293
3294class ArrayAgg(AggFunc):
3295    pass
3296
3297
3298class ArrayAll(Func):
3299    arg_types = {"this": True, "expression": True}
3300
3301
3302class ArrayAny(Func):
3303    arg_types = {"this": True, "expression": True}
3304
3305
3306class ArrayConcat(Func):
3307    arg_types = {"this": True, "expressions": False}
3308    is_var_len_args = True
3309
3310
3311class ArrayContains(Binary, Func):
3312    pass
3313
3314
3315class ArrayContained(Binary):
3316    pass
3317
3318
3319class ArrayFilter(Func):
3320    arg_types = {"this": True, "expression": True}
3321    _sql_names = ["FILTER", "ARRAY_FILTER"]
3322
3323
3324class ArrayJoin(Func):
3325    arg_types = {"this": True, "expression": True, "null": False}
3326
3327
3328class ArraySize(Func):
3329    arg_types = {"this": True, "expression": False}
3330
3331
3332class ArraySort(Func):
3333    arg_types = {"this": True, "expression": False}
3334
3335
3336class ArraySum(Func):
3337    pass
3338
3339
3340class ArrayUnionAgg(AggFunc):
3341    pass
3342
3343
3344class Avg(AggFunc):
3345    pass
3346
3347
3348class AnyValue(AggFunc):
3349    pass
3350
3351
3352class Case(Func):
3353    arg_types = {"this": False, "ifs": True, "default": False}
3354
3355
3356class Cast(Func):
3357    arg_types = {"this": True, "to": True}
3358
3359    @property
3360    def name(self) -> str:
3361        return self.this.name
3362
3363    @property
3364    def to(self):
3365        return self.args["to"]
3366
3367    @property
3368    def output_name(self):
3369        return self.name
3370
3371    def is_type(self, dtype: DataType.Type) -> bool:
3372        return self.to.is_type(dtype)
3373
3374
3375class Collate(Binary):
3376    pass
3377
3378
3379class TryCast(Cast):
3380    pass
3381
3382
3383class Ceil(Func):
3384    arg_types = {"this": True, "decimals": False}
3385    _sql_names = ["CEIL", "CEILING"]
3386
3387
3388class Coalesce(Func):
3389    arg_types = {"this": True, "expressions": False}
3390    is_var_len_args = True
3391
3392
3393class Concat(Func):
3394    arg_types = {"expressions": True}
3395    is_var_len_args = True
3396
3397
3398class ConcatWs(Concat):
3399    _sql_names = ["CONCAT_WS"]
3400
3401
3402class Count(AggFunc):
3403    arg_types = {"this": False}
3404
3405
3406class CountIf(AggFunc):
3407    pass
3408
3409
3410class CurrentDate(Func):
3411    arg_types = {"this": False}
3412
3413
3414class CurrentDatetime(Func):
3415    arg_types = {"this": False}
3416
3417
3418class CurrentTime(Func):
3419    arg_types = {"this": False}
3420
3421
3422class CurrentTimestamp(Func):
3423    arg_types = {"this": False}
3424
3425
3426class DateAdd(Func, TimeUnit):
3427    arg_types = {"this": True, "expression": True, "unit": False}
3428
3429
3430class DateSub(Func, TimeUnit):
3431    arg_types = {"this": True, "expression": True, "unit": False}
3432
3433
3434class DateDiff(Func, TimeUnit):
3435    _sql_names = ["DATEDIFF", "DATE_DIFF"]
3436    arg_types = {"this": True, "expression": True, "unit": False}
3437
3438
3439class DateTrunc(Func):
3440    arg_types = {"unit": True, "this": True, "zone": False}
3441
3442
3443class DatetimeAdd(Func, TimeUnit):
3444    arg_types = {"this": True, "expression": True, "unit": False}
3445
3446
3447class DatetimeSub(Func, TimeUnit):
3448    arg_types = {"this": True, "expression": True, "unit": False}
3449
3450
3451class DatetimeDiff(Func, TimeUnit):
3452    arg_types = {"this": True, "expression": True, "unit": False}
3453
3454
3455class DatetimeTrunc(Func, TimeUnit):
3456    arg_types = {"this": True, "unit": True, "zone": False}
3457
3458
3459class DayOfWeek(Func):
3460    _sql_names = ["DAY_OF_WEEK", "DAYOFWEEK"]
3461
3462
3463class DayOfMonth(Func):
3464    _sql_names = ["DAY_OF_MONTH", "DAYOFMONTH"]
3465
3466
3467class DayOfYear(Func):
3468    _sql_names = ["DAY_OF_YEAR", "DAYOFYEAR"]
3469
3470
3471class WeekOfYear(Func):
3472    _sql_names = ["WEEK_OF_YEAR", "WEEKOFYEAR"]
3473
3474
3475class LastDateOfMonth(Func):
3476    pass
3477
3478
3479class Extract(Func):
3480    arg_types = {"this": True, "expression": True}
3481
3482
3483class TimestampAdd(Func, TimeUnit):
3484    arg_types = {"this": True, "expression": True, "unit": False}
3485
3486
3487class TimestampSub(Func, TimeUnit):
3488    arg_types = {"this": True, "expression": True, "unit": False}
3489
3490
3491class TimestampDiff(Func, TimeUnit):
3492    arg_types = {"this": True, "expression": True, "unit": False}
3493
3494
3495class TimestampTrunc(Func, TimeUnit):
3496    arg_types = {"this": True, "unit": True, "zone": False}
3497
3498
3499class TimeAdd(Func, TimeUnit):
3500    arg_types = {"this": True, "expression": True, "unit": False}
3501
3502
3503class TimeSub(Func, TimeUnit):
3504    arg_types = {"this": True, "expression": True, "unit": False}
3505
3506
3507class TimeDiff(Func, TimeUnit):
3508    arg_types = {"this": True, "expression": True, "unit": False}
3509
3510
3511class TimeTrunc(Func, TimeUnit):
3512    arg_types = {"this": True, "unit": True, "zone": False}
3513
3514
3515class DateFromParts(Func):
3516    _sql_names = ["DATEFROMPARTS"]
3517    arg_types = {"year": True, "month": True, "day": True}
3518
3519
3520class DateStrToDate(Func):
3521    pass
3522
3523
3524class DateToDateStr(Func):
3525    pass
3526
3527
3528class DateToDi(Func):
3529    pass
3530
3531
3532class Day(Func):
3533    pass
3534
3535
3536class Decode(Func):
3537    arg_types = {"this": True, "charset": True, "replace": False}
3538
3539
3540class DiToDate(Func):
3541    pass
3542
3543
3544class Encode(Func):
3545    arg_types = {"this": True, "charset": True}
3546
3547
3548class Exp(Func):
3549    pass
3550
3551
3552class Explode(Func):
3553    pass
3554
3555
3556class ExponentialTimeDecayedAvg(AggFunc):
3557    arg_types = {"this": True, "time": False, "decay": False}
3558
3559
3560class Floor(Func):
3561    arg_types = {"this": True, "decimals": False}
3562
3563
3564class Greatest(Func):
3565    arg_types = {"this": True, "expressions": False}
3566    is_var_len_args = True
3567
3568
3569class GroupConcat(Func):
3570    arg_types = {"this": True, "separator": False}
3571
3572
3573class GroupUniqArray(AggFunc):
3574    arg_types = {"this": True, "size": False}
3575
3576
3577class Hex(Func):
3578    pass
3579
3580
3581class Histogram(AggFunc):
3582    arg_types = {"this": True, "bins": False}
3583
3584
3585class If(Func):
3586    arg_types = {"this": True, "true": True, "false": False}
3587
3588
3589class IfNull(Func):
3590    arg_types = {"this": True, "expression": False}
3591    _sql_names = ["IFNULL", "NVL"]
3592
3593
3594class Initcap(Func):
3595    pass
3596
3597
3598class JSONBContains(Binary):
3599    _sql_names = ["JSONB_CONTAINS"]
3600
3601
3602class JSONExtract(Binary, Func):
3603    _sql_names = ["JSON_EXTRACT"]
3604
3605
3606class JSONExtractScalar(JSONExtract):
3607    _sql_names = ["JSON_EXTRACT_SCALAR"]
3608
3609
3610class JSONBExtract(JSONExtract):
3611    _sql_names = ["JSONB_EXTRACT"]
3612
3613
3614class JSONBExtractScalar(JSONExtract):
3615    _sql_names = ["JSONB_EXTRACT_SCALAR"]
3616
3617
3618class Least(Func):
3619    arg_types = {"expressions": False}
3620    is_var_len_args = True
3621
3622
3623class Length(Func):
3624    pass
3625
3626
3627class Levenshtein(Func):
3628    arg_types = {
3629        "this": True,
3630        "expression": False,
3631        "ins_cost": False,
3632        "del_cost": False,
3633        "sub_cost": False,
3634    }
3635
3636
3637class Ln(Func):
3638    pass
3639
3640
3641class Log(Func):
3642    arg_types = {"this": True, "expression": False}
3643
3644
3645class Log2(Func):
3646    pass
3647
3648
3649class Log10(Func):
3650    pass
3651
3652
3653class LogicalOr(AggFunc):
3654    _sql_names = ["LOGICAL_OR", "BOOL_OR", "BOOLOR_AGG"]
3655
3656
3657class LogicalAnd(AggFunc):
3658    _sql_names = ["LOGICAL_AND", "BOOL_AND", "BOOLAND_AGG"]
3659
3660
3661class Lower(Func):
3662    _sql_names = ["LOWER", "LCASE"]
3663
3664
3665class Map(Func):
3666    arg_types = {"keys": False, "values": False}
3667
3668
3669class VarMap(Func):
3670    arg_types = {"keys": True, "values": True}
3671    is_var_len_args = True
3672
3673
3674class Matches(Func):
3675    """Oracle/Snowflake decode.
3676    https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions040.htm
3677    Pattern matching MATCHES(value, search1, result1, ...searchN, resultN, else)
3678    """
3679
3680    arg_types = {"this": True, "expressions": True}
3681    is_var_len_args = True
3682
3683
3684class Max(AggFunc):
3685    arg_types = {"this": True, "expressions": False}
3686    is_var_len_args = True
3687
3688
3689class Min(AggFunc):
3690    arg_types = {"this": True, "expressions": False}
3691    is_var_len_args = True
3692
3693
3694class Month(Func):
3695    pass
3696
3697
3698class Nvl2(Func):
3699    arg_types = {"this": True, "true": True, "false": False}
3700
3701
3702class Posexplode(Func):
3703    pass
3704
3705
3706class Pow(Binary, Func):
3707    _sql_names = ["POWER", "POW"]
3708
3709
3710class PercentileCont(AggFunc):
3711    pass
3712
3713
3714class PercentileDisc(AggFunc):
3715    pass
3716
3717
3718class Quantile(AggFunc):
3719    arg_types = {"this": True, "quantile": True}
3720
3721
3722# Clickhouse-specific:
3723# https://clickhouse.com/docs/en/sql-reference/aggregate-functions/reference/quantiles/#quantiles
3724class Quantiles(AggFunc):
3725    arg_types = {"parameters": True, "expressions": True}
3726    is_var_len_args = True
3727
3728
3729class QuantileIf(AggFunc):
3730    arg_types = {"parameters": True, "expressions": True}
3731
3732
3733class ApproxQuantile(Quantile):
3734    arg_types = {"this": True, "quantile": True, "accuracy": False, "weight": False}
3735
3736
3737class RangeN(Func):
3738    arg_types = {"this": True, "expressions": True, "each": False}
3739
3740
3741class ReadCSV(Func):
3742    _sql_names = ["READ_CSV"]
3743    is_var_len_args = True
3744    arg_types = {"this": True, "expressions": False}
3745
3746
3747class Reduce(Func):
3748    arg_types = {"this": True, "initial": True, "merge": True, "finish": False}
3749
3750
3751class RegexpExtract(Func):
3752    arg_types = {
3753        "this": True,
3754        "expression": True,
3755        "position": False,
3756        "occurrence": False,
3757        "group": False,
3758    }
3759
3760
3761class RegexpLike(Func):
3762    arg_types = {"this": True, "expression": True, "flag": False}
3763
3764
3765class RegexpILike(Func):
3766    arg_types = {"this": True, "expression": True, "flag": False}
3767
3768
3769class RegexpSplit(Func):
3770    arg_types = {"this": True, "expression": True}
3771
3772
3773class Repeat(Func):
3774    arg_types = {"this": True, "times": True}
3775
3776
3777class Round(Func):
3778    arg_types = {"this": True, "decimals": False}
3779
3780
3781class RowNumber(Func):
3782    arg_types: t.Dict[str, t.Any] = {}
3783
3784
3785class SafeDivide(Func):
3786    arg_types = {"this": True, "expression": True}
3787
3788
3789class SetAgg(AggFunc):
3790    pass
3791
3792
3793class SortArray(Func):
3794    arg_types = {"this": True, "asc": False}
3795
3796
3797class Split(Func):
3798    arg_types = {"this": True, "expression": True, "limit": False}
3799
3800
3801# Start may be omitted in the case of postgres
3802# https://www.postgresql.org/docs/9.1/functions-string.html @ Table 9-6
3803class Substring(Func):
3804    arg_types = {"this": True, "start": False, "length": False}
3805
3806
3807class StrPosition(Func):
3808    arg_types = {
3809        "this": True,
3810        "substr": True,
3811        "position": False,
3812        "instance": False,
3813    }
3814
3815
3816class StrToDate(Func):
3817    arg_types = {"this": True, "format": True}
3818
3819
3820class StrToTime(Func):
3821    arg_types = {"this": True, "format": True}
3822
3823
3824# Spark allows unix_timestamp()
3825# https://spark.apache.org/docs/3.1.3/api/python/reference/api/pyspark.sql.functions.unix_timestamp.html
3826class StrToUnix(Func):
3827    arg_types = {"this": False, "format": False}
3828
3829
3830class NumberToStr(Func):
3831    arg_types = {"this": True, "format": True}
3832
3833
3834class Struct(Func):
3835    arg_types = {"expressions": True}
3836    is_var_len_args = True
3837
3838
3839class StructExtract(Func):
3840    arg_types = {"this": True, "expression": True}
3841
3842
3843class Sum(AggFunc):
3844    pass
3845
3846
3847class Sqrt(Func):
3848    pass
3849
3850
3851class Stddev(AggFunc):
3852    pass
3853
3854
3855class StddevPop(AggFunc):
3856    pass
3857
3858
3859class StddevSamp(AggFunc):
3860    pass
3861
3862
3863class TimeToStr(Func):
3864    arg_types = {"this": True, "format": True}
3865
3866
3867class TimeToTimeStr(Func):
3868    pass
3869
3870
3871class TimeToUnix(Func):
3872    pass
3873
3874
3875class TimeStrToDate(Func):
3876    pass
3877
3878
3879class TimeStrToTime(Func):
3880    pass
3881
3882
3883class TimeStrToUnix(Func):
3884    pass
3885
3886
3887class Trim(Func):
3888    arg_types = {
3889        "this": True,
3890        "expression": False,
3891        "position": False,
3892        "collation": False,
3893    }
3894
3895
3896class TsOrDsAdd(Func, TimeUnit):
3897    arg_types = {"this": True, "expression": True, "unit": False}
3898
3899
3900class TsOrDsToDateStr(Func):
3901    pass
3902
3903
3904class TsOrDsToDate(Func):
3905    arg_types = {"this": True, "format": False}
3906
3907
3908class TsOrDiToDi(Func):
3909    pass
3910
3911
3912class Unhex(Func):
3913    pass
3914
3915
3916class UnixToStr(Func):
3917    arg_types = {"this": True, "format": False}
3918
3919
3920# https://prestodb.io/docs/current/functions/datetime.html
3921# presto has weird zone/hours/minutes
3922class UnixToTime(Func):
3923    arg_types = {"this": True, "scale": False, "zone": False, "hours": False, "minutes": False}
3924
3925    SECONDS = Literal.string("seconds")
3926    MILLIS = Literal.string("millis")
3927    MICROS = Literal.string("micros")
3928
3929
3930class UnixToTimeStr(Func):
3931    pass
3932
3933
3934class Upper(Func):
3935    _sql_names = ["UPPER", "UCASE"]
3936
3937
3938class Variance(AggFunc):
3939    _sql_names = ["VARIANCE", "VARIANCE_SAMP", "VAR_SAMP"]
3940
3941
3942class VariancePop(AggFunc):
3943    _sql_names = ["VARIANCE_POP", "VAR_POP"]
3944
3945
3946class Week(Func):
3947    arg_types = {"this": True, "mode": False}
3948
3949
3950class XMLTable(Func):
3951    arg_types = {"this": True, "passing": False, "columns": False, "by_ref": False}
3952
3953
3954class Year(Func):
3955    pass
3956
3957
3958class Use(Expression):
3959    arg_types = {"this": True, "kind": False}
3960
3961
3962class Merge(Expression):
3963    arg_types = {"this": True, "using": True, "on": True, "expressions": True}
3964
3965
3966class When(Func):
3967    arg_types = {"matched": True, "source": False, "condition": False, "then": True}
3968
3969
3970def _norm_args(expression):
3971    args = {}
3972
3973    for k, arg in expression.args.items():
3974        if isinstance(arg, list):
3975            arg = [_norm_arg(a) for a in arg]
3976            if not arg:
3977                arg = None
3978        else:
3979            arg = _norm_arg(arg)
3980
3981        if arg is not None and arg is not False:
3982            args[k] = arg
3983
3984    return args
3985
3986
3987def _norm_arg(arg):
3988    return arg.lower() if isinstance(arg, str) else arg
3989
3990
3991ALL_FUNCTIONS = subclasses(__name__, Func, (AggFunc, Anonymous, Func))
3992
3993
3994# Helpers
3995def maybe_parse(
3996    sql_or_expression: ExpOrStr,
3997    *,
3998    into: t.Optional[IntoType] = None,
3999    dialect: DialectType = None,
4000    prefix: t.Optional[str] = None,
4001    copy: bool = False,
4002    **opts,
4003) -> Expression:
4004    """Gracefully handle a possible string or expression.
4005
4006    Example:
4007        >>> maybe_parse("1")
4008        (LITERAL this: 1, is_string: False)
4009        >>> maybe_parse(to_identifier("x"))
4010        (IDENTIFIER this: x, quoted: False)
4011
4012    Args:
4013        sql_or_expression: the SQL code string or an expression
4014        into: the SQLGlot Expression to parse into
4015        dialect: the dialect used to parse the input expressions (in the case that an
4016            input expression is a SQL string).
4017        prefix: a string to prefix the sql with before it gets parsed
4018            (automatically includes a space)
4019        copy: whether or not to copy the expression.
4020        **opts: other options to use to parse the input expressions (again, in the case
4021            that an input expression is a SQL string).
4022
4023    Returns:
4024        Expression: the parsed or given expression.
4025    """
4026    if isinstance(sql_or_expression, Expression):
4027        if copy:
4028            return sql_or_expression.copy()
4029        return sql_or_expression
4030
4031    import sqlglot
4032
4033    sql = str(sql_or_expression)
4034    if prefix:
4035        sql = f"{prefix} {sql}"
4036    return sqlglot.parse_one(sql, read=dialect, into=into, **opts)
4037
4038
4039def _maybe_copy(instance, copy=True):
4040    return instance.copy() if copy else instance
4041
4042
4043def _is_wrong_expression(expression, into):
4044    return isinstance(expression, Expression) and not isinstance(expression, into)
4045
4046
4047def _apply_builder(
4048    expression,
4049    instance,
4050    arg,
4051    copy=True,
4052    prefix=None,
4053    into=None,
4054    dialect=None,
4055    **opts,
4056):
4057    if _is_wrong_expression(expression, into):
4058        expression = into(this=expression)
4059    instance = _maybe_copy(instance, copy)
4060    expression = maybe_parse(
4061        sql_or_expression=expression,
4062        prefix=prefix,
4063        into=into,
4064        dialect=dialect,
4065        **opts,
4066    )
4067    instance.set(arg, expression)
4068    return instance
4069
4070
4071def _apply_child_list_builder(
4072    *expressions,
4073    instance,
4074    arg,
4075    append=True,
4076    copy=True,
4077    prefix=None,
4078    into=None,
4079    dialect=None,
4080    properties=None,
4081    **opts,
4082):
4083    instance = _maybe_copy(instance, copy)
4084    parsed = []
4085    for expression in expressions:
4086        if _is_wrong_expression(expression, into):
4087            expression = into(expressions=[expression])
4088        expression = maybe_parse(
4089            expression,
4090            into=into,
4091            dialect=dialect,
4092            prefix=prefix,
4093            **opts,
4094        )
4095        parsed.extend(expression.expressions)
4096
4097    existing = instance.args.get(arg)
4098    if append and existing:
4099        parsed = existing.expressions + parsed
4100
4101    child = into(expressions=parsed)
4102    for k, v in (properties or {}).items():
4103        child.set(k, v)
4104    instance.set(arg, child)
4105    return instance
4106
4107
4108def _apply_list_builder(
4109    *expressions,
4110    instance,
4111    arg,
4112    append=True,
4113    copy=True,
4114    prefix=None,
4115    into=None,
4116    dialect=None,
4117    **opts,
4118):
4119    inst = _maybe_copy(instance, copy)
4120
4121    expressions = [
4122        maybe_parse(
4123            sql_or_expression=expression,
4124            into=into,
4125            prefix=prefix,
4126            dialect=dialect,
4127            **opts,
4128        )
4129        for expression in expressions
4130    ]
4131
4132    existing_expressions = inst.args.get(arg)
4133    if append and existing_expressions:
4134        expressions = existing_expressions + expressions
4135
4136    inst.set(arg, expressions)
4137    return inst
4138
4139
4140def _apply_conjunction_builder(
4141    *expressions,
4142    instance,
4143    arg,
4144    into=None,
4145    append=True,
4146    copy=True,
4147    dialect=None,
4148    **opts,
4149):
4150    expressions = [exp for exp in expressions if exp is not None and exp != ""]
4151    if not expressions:
4152        return instance
4153
4154    inst = _maybe_copy(instance, copy)
4155
4156    existing = inst.args.get(arg)
4157    if append and existing is not None:
4158        expressions = [existing.this if into else existing] + list(expressions)
4159
4160    node = and_(*expressions, dialect=dialect, **opts)
4161
4162    inst.set(arg, into(this=node) if into else node)
4163    return inst
4164
4165
4166def _combine(expressions, operator, dialect=None, **opts):
4167    expressions = [condition(expression, dialect=dialect, **opts) for expression in expressions]
4168    this = expressions[0]
4169    if expressions[1:]:
4170        this = _wrap_operator(this)
4171    for expression in expressions[1:]:
4172        this = operator(this=this, expression=_wrap_operator(expression))
4173    return this
4174
4175
4176def _wrap_operator(expression):
4177    if isinstance(expression, (And, Or, Not)):
4178        expression = Paren(this=expression)
4179    return expression
4180
4181
4182def union(left, right, distinct=True, dialect=None, **opts):
4183    """
4184    Initializes a syntax tree from one UNION expression.
4185
4186    Example:
4187        >>> union("SELECT * FROM foo", "SELECT * FROM bla").sql()
4188        'SELECT * FROM foo UNION SELECT * FROM bla'
4189
4190    Args:
4191        left (str | Expression): the SQL code string corresponding to the left-hand side.
4192            If an `Expression` instance is passed, it will be used as-is.
4193        right (str | Expression): the SQL code string corresponding to the right-hand side.
4194            If an `Expression` instance is passed, it will be used as-is.
4195        distinct (bool): set the DISTINCT flag if and only if this is true.
4196        dialect (str): the dialect used to parse the input expression.
4197        opts (kwargs): other options to use to parse the input expressions.
4198    Returns:
4199        Union: the syntax tree for the UNION expression.
4200    """
4201    left = maybe_parse(sql_or_expression=left, dialect=dialect, **opts)
4202    right = maybe_parse(sql_or_expression=right, dialect=dialect, **opts)
4203
4204    return Union(this=left, expression=right, distinct=distinct)
4205
4206
4207def intersect(left, right, distinct=True, dialect=None, **opts):
4208    """
4209    Initializes a syntax tree from one INTERSECT expression.
4210
4211    Example:
4212        >>> intersect("SELECT * FROM foo", "SELECT * FROM bla").sql()
4213        'SELECT * FROM foo INTERSECT SELECT * FROM bla'
4214
4215    Args:
4216        left (str | Expression): the SQL code string corresponding to the left-hand side.
4217            If an `Expression` instance is passed, it will be used as-is.
4218        right (str | Expression): the SQL code string corresponding to the right-hand side.
4219            If an `Expression` instance is passed, it will be used as-is.
4220        distinct (bool): set the DISTINCT flag if and only if this is true.
4221        dialect (str): the dialect used to parse the input expression.
4222        opts (kwargs): other options to use to parse the input expressions.
4223    Returns:
4224        Intersect: the syntax tree for the INTERSECT expression.
4225    """
4226    left = maybe_parse(sql_or_expression=left, dialect=dialect, **opts)
4227    right = maybe_parse(sql_or_expression=right, dialect=dialect, **opts)
4228
4229    return Intersect(this=left, expression=right, distinct=distinct)
4230
4231
4232def except_(left, right, distinct=True, dialect=None, **opts):
4233    """
4234    Initializes a syntax tree from one EXCEPT expression.
4235
4236    Example:
4237        >>> except_("SELECT * FROM foo", "SELECT * FROM bla").sql()
4238        'SELECT * FROM foo EXCEPT SELECT * FROM bla'
4239
4240    Args:
4241        left (str | Expression): the SQL code string corresponding to the left-hand side.
4242            If an `Expression` instance is passed, it will be used as-is.
4243        right (str | Expression): the SQL code string corresponding to the right-hand side.
4244            If an `Expression` instance is passed, it will be used as-is.
4245        distinct (bool): set the DISTINCT flag if and only if this is true.
4246        dialect (str): the dialect used to parse the input expression.
4247        opts (kwargs): other options to use to parse the input expressions.
4248    Returns:
4249        Except: the syntax tree for the EXCEPT statement.
4250    """
4251    left = maybe_parse(sql_or_expression=left, dialect=dialect, **opts)
4252    right = maybe_parse(sql_or_expression=right, dialect=dialect, **opts)
4253
4254    return Except(this=left, expression=right, distinct=distinct)
4255
4256
4257def select(*expressions: ExpOrStr, dialect: DialectType = None, **opts) -> Select:
4258    """
4259    Initializes a syntax tree from one or multiple SELECT expressions.
4260
4261    Example:
4262        >>> select("col1", "col2").from_("tbl").sql()
4263        'SELECT col1, col2 FROM tbl'
4264
4265    Args:
4266        *expressions: the SQL code string to parse as the expressions of a
4267            SELECT statement. If an Expression instance is passed, this is used as-is.
4268        dialect: the dialect used to parse the input expressions (in the case that an
4269            input expression is a SQL string).
4270        **opts: other options to use to parse the input expressions (again, in the case
4271            that an input expression is a SQL string).
4272
4273    Returns:
4274        Select: the syntax tree for the SELECT statement.
4275    """
4276    return Select().select(*expressions, dialect=dialect, **opts)
4277
4278
4279def from_(*expressions, dialect=None, **opts) -> Select:
4280    """
4281    Initializes a syntax tree from a FROM expression.
4282
4283    Example:
4284        >>> from_("tbl").select("col1", "col2").sql()
4285        'SELECT col1, col2 FROM tbl'
4286
4287    Args:
4288        *expressions (str | Expression): the SQL code string to parse as the FROM expressions of a
4289            SELECT statement. If an Expression instance is passed, this is used as-is.
4290        dialect (str): the dialect used to parse the input expression (in the case that the
4291            input expression is a SQL string).
4292        **opts: other options to use to parse the input expressions (again, in the case
4293            that the input expression is a SQL string).
4294
4295    Returns:
4296        Select: the syntax tree for the SELECT statement.
4297    """
4298    return Select().from_(*expressions, dialect=dialect, **opts)
4299
4300
4301def update(
4302    table: str | Table,
4303    properties: dict,
4304    where: t.Optional[ExpOrStr] = None,
4305    from_: t.Optional[ExpOrStr] = None,
4306    dialect: DialectType = None,
4307    **opts,
4308) -> Update:
4309    """
4310    Creates an update statement.
4311
4312    Example:
4313        >>> update("my_table", {"x": 1, "y": "2", "z": None}, from_="baz", where="id > 1").sql()
4314        "UPDATE my_table SET x = 1, y = '2', z = NULL FROM baz WHERE id > 1"
4315
4316    Args:
4317        *properties: dictionary of properties to set which are
4318            auto converted to sql objects eg None -> NULL
4319        where: sql conditional parsed into a WHERE statement
4320        from_: sql statement parsed into a FROM statement
4321        dialect: the dialect used to parse the input expressions.
4322        **opts: other options to use to parse the input expressions.
4323
4324    Returns:
4325        Update: the syntax tree for the UPDATE statement.
4326    """
4327    update_expr = Update(this=maybe_parse(table, into=Table, dialect=dialect))
4328    update_expr.set(
4329        "expressions",
4330        [
4331            EQ(this=maybe_parse(k, dialect=dialect, **opts), expression=convert(v))
4332            for k, v in properties.items()
4333        ],
4334    )
4335    if from_:
4336        update_expr.set(
4337            "from",
4338            maybe_parse(from_, into=From, dialect=dialect, prefix="FROM", **opts),
4339        )
4340    if isinstance(where, Condition):
4341        where = Where(this=where)
4342    if where:
4343        update_expr.set(
4344            "where",
4345            maybe_parse(where, into=Where, dialect=dialect, prefix="WHERE", **opts),
4346        )
4347    return update_expr
4348
4349
4350def delete(
4351    table: ExpOrStr,
4352    where: t.Optional[ExpOrStr] = None,
4353    returning: t.Optional[ExpOrStr] = None,
4354    dialect: DialectType = None,
4355    **opts,
4356) -> Delete:
4357    """
4358    Builds a delete statement.
4359
4360    Example:
4361        >>> delete("my_table", where="id > 1").sql()
4362        'DELETE FROM my_table WHERE id > 1'
4363
4364    Args:
4365        where: sql conditional parsed into a WHERE statement
4366        returning: sql conditional parsed into a RETURNING statement
4367        dialect: the dialect used to parse the input expressions.
4368        **opts: other options to use to parse the input expressions.
4369
4370    Returns:
4371        Delete: the syntax tree for the DELETE statement.
4372    """
4373    delete_expr = Delete().delete(table, dialect=dialect, copy=False, **opts)
4374    if where:
4375        delete_expr = delete_expr.where(where, dialect=dialect, copy=False, **opts)
4376    if returning:
4377        delete_expr = delete_expr.returning(returning, dialect=dialect, copy=False, **opts)
4378    return delete_expr
4379
4380
4381def condition(expression, dialect=None, **opts) -> Condition:
4382    """
4383    Initialize a logical condition expression.
4384
4385    Example:
4386        >>> condition("x=1").sql()
4387        'x = 1'
4388
4389        This is helpful for composing larger logical syntax trees:
4390        >>> where = condition("x=1")
4391        >>> where = where.and_("y=1")
4392        >>> Select().from_("tbl").select("*").where(where).sql()
4393        'SELECT * FROM tbl WHERE x = 1 AND y = 1'
4394
4395    Args:
4396        *expression (str | Expression): the SQL code string to parse.
4397            If an Expression instance is passed, this is used as-is.
4398        dialect (str): the dialect used to parse the input expression (in the case that the
4399            input expression is a SQL string).
4400        **opts: other options to use to parse the input expressions (again, in the case
4401            that the input expression is a SQL string).
4402
4403    Returns:
4404        Condition: the expression
4405    """
4406    return maybe_parse(  # type: ignore
4407        expression,
4408        into=Condition,
4409        dialect=dialect,
4410        **opts,
4411    )
4412
4413
4414def and_(*expressions, dialect=None, **opts) -> And:
4415    """
4416    Combine multiple conditions with an AND logical operator.
4417
4418    Example:
4419        >>> and_("x=1", and_("y=1", "z=1")).sql()
4420        'x = 1 AND (y = 1 AND z = 1)'
4421
4422    Args:
4423        *expressions (str | Expression): the SQL code strings to parse.
4424            If an Expression instance is passed, this is used as-is.
4425        dialect (str): the dialect used to parse the input expression.
4426        **opts: other options to use to parse the input expressions.
4427
4428    Returns:
4429        And: the new condition
4430    """
4431    return _combine(expressions, And, dialect, **opts)
4432
4433
4434def or_(*expressions, dialect=None, **opts) -> Or:
4435    """
4436    Combine multiple conditions with an OR logical operator.
4437
4438    Example:
4439        >>> or_("x=1", or_("y=1", "z=1")).sql()
4440        'x = 1 OR (y = 1 OR z = 1)'
4441
4442    Args:
4443        *expressions (str | Expression): the SQL code strings to parse.
4444            If an Expression instance is passed, this is used as-is.
4445        dialect (str): the dialect used to parse the input expression.
4446        **opts: other options to use to parse the input expressions.
4447
4448    Returns:
4449        Or: the new condition
4450    """
4451    return _combine(expressions, Or, dialect, **opts)
4452
4453
4454def not_(expression, dialect=None, **opts) -> Not:
4455    """
4456    Wrap a condition with a NOT operator.
4457
4458    Example:
4459        >>> not_("this_suit='black'").sql()
4460        "NOT this_suit = 'black'"
4461
4462    Args:
4463        expression (str | Expression): the SQL code strings to parse.
4464            If an Expression instance is passed, this is used as-is.
4465        dialect (str): the dialect used to parse the input expression.
4466        **opts: other options to use to parse the input expressions.
4467
4468    Returns:
4469        Not: the new condition
4470    """
4471    this = condition(
4472        expression,
4473        dialect=dialect,
4474        **opts,
4475    )
4476    return Not(this=_wrap_operator(this))
4477
4478
4479def paren(expression) -> Paren:
4480    return Paren(this=expression)
4481
4482
4483SAFE_IDENTIFIER_RE = re.compile(r"^[_a-zA-Z][\w]*$")
4484
4485
4486@t.overload
4487def to_identifier(name: None, quoted: t.Optional[bool] = None) -> None:
4488    ...
4489
4490
4491@t.overload
4492def to_identifier(name: str | Identifier, quoted: t.Optional[bool] = None) -> Identifier:
4493    ...
4494
4495
4496def to_identifier(name, quoted=None):
4497    """Builds an identifier.
4498
4499    Args:
4500        name: The name to turn into an identifier.
4501        quoted: Whether or not force quote the identifier.
4502
4503    Returns:
4504        The identifier ast node.
4505    """
4506
4507    if name is None:
4508        return None
4509
4510    if isinstance(name, Identifier):
4511        identifier = name
4512    elif isinstance(name, str):
4513        identifier = Identifier(
4514            this=name,
4515            quoted=not re.match(SAFE_IDENTIFIER_RE, name) if quoted is None else quoted,
4516        )
4517    else:
4518        raise ValueError(f"Name needs to be a string or an Identifier, got: {name.__class__}")
4519    return identifier
4520
4521
4522INTERVAL_STRING_RE = re.compile(r"\s*([0-9]+)\s*([a-zA-Z]+)\s*")
4523
4524
4525def to_interval(interval: str | Literal) -> Interval:
4526    """Builds an interval expression from a string like '1 day' or '5 months'."""
4527    if isinstance(interval, Literal):
4528        if not interval.is_string:
4529            raise ValueError("Invalid interval string.")
4530
4531        interval = interval.this
4532
4533    interval_parts = INTERVAL_STRING_RE.match(interval)  # type: ignore
4534
4535    if not interval_parts:
4536        raise ValueError("Invalid interval string.")
4537
4538    return Interval(
4539        this=Literal.string(interval_parts.group(1)),
4540        unit=Var(this=interval_parts.group(2)),
4541    )
4542
4543
4544@t.overload
4545def to_table(sql_path: str | Table, **kwargs) -> Table:
4546    ...
4547
4548
4549@t.overload
4550def to_table(sql_path: None, **kwargs) -> None:
4551    ...
4552
4553
4554def to_table(sql_path: t.Optional[str | Table], **kwargs) -> t.Optional[Table]:
4555    """
4556    Create a table expression from a `[catalog].[schema].[table]` sql path. Catalog and schema are optional.
4557    If a table is passed in then that table is returned.
4558
4559    Args:
4560        sql_path: a `[catalog].[schema].[table]` string.
4561
4562    Returns:
4563        A table expression.
4564    """
4565    if sql_path is None or isinstance(sql_path, Table):
4566        return sql_path
4567    if not isinstance(sql_path, str):
4568        raise ValueError(f"Invalid type provided for a table: {type(sql_path)}")
4569
4570    catalog, db, table_name = (to_identifier(x) for x in split_num_words(sql_path, ".", 3))
4571    return Table(this=table_name, db=db, catalog=catalog, **kwargs)
4572
4573
4574def to_column(sql_path: str | Column, **kwargs) -> Column:
4575    """
4576    Create a column from a `[table].[column]` sql path. Schema is optional.
4577
4578    If a column is passed in then that column is returned.
4579
4580    Args:
4581        sql_path: `[table].[column]` string
4582    Returns:
4583        Table: A column expression
4584    """
4585    if sql_path is None or isinstance(sql_path, Column):
4586        return sql_path
4587    if not isinstance(sql_path, str):
4588        raise ValueError(f"Invalid type provided for column: {type(sql_path)}")
4589    table_name, column_name = (to_identifier(x) for x in split_num_words(sql_path, ".", 2))
4590    return Column(this=column_name, table=table_name, **kwargs)
4591
4592
4593def alias_(
4594    expression: ExpOrStr,
4595    alias: str | Identifier,
4596    table: bool | t.Sequence[str | Identifier] = False,
4597    quoted: t.Optional[bool] = None,
4598    dialect: DialectType = None,
4599    **opts,
4600):
4601    """Create an Alias expression.
4602
4603    Example:
4604        >>> alias_('foo', 'bar').sql()
4605        'foo AS bar'
4606
4607        >>> alias_('(select 1, 2)', 'bar', table=['a', 'b']).sql()
4608        '(SELECT 1, 2) AS bar(a, b)'
4609
4610    Args:
4611        expression: the SQL code strings to parse.
4612            If an Expression instance is passed, this is used as-is.
4613        alias: the alias name to use. If the name has
4614            special characters it is quoted.
4615        table: Whether or not to create a table alias, can also be a list of columns.
4616        quoted: whether or not to quote the alias
4617        dialect: the dialect used to parse the input expression.
4618        **opts: other options to use to parse the input expressions.
4619
4620    Returns:
4621        Alias: the aliased expression
4622    """
4623    exp = maybe_parse(expression, dialect=dialect, **opts)
4624    alias = to_identifier(alias, quoted=quoted)
4625
4626    if table:
4627        table_alias = TableAlias(this=alias)
4628        exp.set("alias", table_alias)
4629
4630        if not isinstance(table, bool):
4631            for column in table:
4632                table_alias.append("columns", to_identifier(column, quoted=quoted))
4633
4634        return exp
4635
4636    # We don't set the "alias" arg for Window expressions, because that would add an IDENTIFIER node in
4637    # the AST, representing a "named_window" [1] construct (eg. bigquery). What we want is an ALIAS node
4638    # for the complete Window expression.
4639    #
4640    # [1]: https://cloud.google.com/bigquery/docs/reference/standard-sql/window-function-calls
4641
4642    if "alias" in exp.arg_types and not isinstance(exp, Window):
4643        exp = exp.copy()
4644        exp.set("alias", alias)
4645        return exp
4646    return Alias(this=exp, alias=alias)
4647
4648
4649def subquery(expression, alias=None, dialect=None, **opts):
4650    """
4651    Build a subquery expression.
4652
4653    Example:
4654        >>> subquery('select x from tbl', 'bar').select('x').sql()
4655        'SELECT x FROM (SELECT x FROM tbl) AS bar'
4656
4657    Args:
4658        expression (str | Expression): the SQL code strings to parse.
4659            If an Expression instance is passed, this is used as-is.
4660        alias (str | Expression): the alias name to use.
4661        dialect (str): the dialect used to parse the input expression.
4662        **opts: other options to use to parse the input expressions.
4663
4664    Returns:
4665        Select: a new select with the subquery expression included
4666    """
4667
4668    expression = maybe_parse(expression, dialect=dialect, **opts).subquery(alias)
4669    return Select().from_(expression, dialect=dialect, **opts)
4670
4671
4672def column(
4673    col: str | Identifier,
4674    table: t.Optional[str | Identifier] = None,
4675    schema: t.Optional[str | Identifier] = None,
4676    quoted: t.Optional[bool] = None,
4677) -> Column:
4678    """
4679    Build a Column.
4680
4681    Args:
4682        col: column name
4683        table: table name
4684        schema: schema name
4685        quoted: whether or not to force quote each part
4686    Returns:
4687        Column: column instance
4688    """
4689    return Column(
4690        this=to_identifier(col, quoted=quoted),
4691        table=to_identifier(table, quoted=quoted),
4692        schema=to_identifier(schema, quoted=quoted),
4693    )
4694
4695
4696def cast(expression: ExpOrStr, to: str | DataType | DataType.Type, **opts) -> Cast:
4697    """Cast an expression to a data type.
4698
4699    Example:
4700        >>> cast('x + 1', 'int').sql()
4701        'CAST(x + 1 AS INT)'
4702
4703    Args:
4704        expression: The expression to cast.
4705        to: The datatype to cast to.
4706
4707    Returns:
4708        A cast node.
4709    """
4710    expression = maybe_parse(expression, **opts)
4711    return Cast(this=expression, to=DataType.build(to, **opts))
4712
4713
4714def table_(table, db=None, catalog=None, quoted=None, alias=None) -> Table:
4715    """Build a Table.
4716
4717    Args:
4718        table (str | Expression): column name
4719        db (str | Expression): db name
4720        catalog (str | Expression): catalog name
4721
4722    Returns:
4723        Table: table instance
4724    """
4725    return Table(
4726        this=to_identifier(table, quoted=quoted),
4727        db=to_identifier(db, quoted=quoted),
4728        catalog=to_identifier(catalog, quoted=quoted),
4729        alias=TableAlias(this=to_identifier(alias)) if alias else None,
4730    )
4731
4732
4733def values(
4734    values: t.Iterable[t.Tuple[t.Any, ...]],
4735    alias: t.Optional[str] = None,
4736    columns: t.Optional[t.Iterable[str] | t.Dict[str, DataType]] = None,
4737) -> Values:
4738    """Build VALUES statement.
4739
4740    Example:
4741        >>> values([(1, '2')]).sql()
4742        "VALUES (1, '2')"
4743
4744    Args:
4745        values: values statements that will be converted to SQL
4746        alias: optional alias
4747        columns: Optional list of ordered column names or ordered dictionary of column names to types.
4748         If either are provided then an alias is also required.
4749         If a dictionary is provided then the first column of the values will be casted to the expected type
4750         in order to help with type inference.
4751
4752    Returns:
4753        Values: the Values expression object
4754    """
4755    if columns and not alias:
4756        raise ValueError("Alias is required when providing columns")
4757    table_alias = (
4758        TableAlias(this=to_identifier(alias), columns=[to_identifier(x) for x in columns])
4759        if columns
4760        else TableAlias(this=to_identifier(alias) if alias else None)
4761    )
4762    expressions = [convert(tup) for tup in values]
4763    if columns and isinstance(columns, dict):
4764        types = list(columns.values())
4765        expressions[0].set(
4766            "expressions",
4767            [cast(x, types[i]) for i, x in enumerate(expressions[0].expressions)],
4768        )
4769    return Values(
4770        expressions=expressions,
4771        alias=table_alias,
4772    )
4773
4774
4775def var(name: t.Optional[ExpOrStr]) -> Var:
4776    """Build a SQL variable.
4777
4778    Example:
4779        >>> repr(var('x'))
4780        '(VAR this: x)'
4781
4782        >>> repr(var(column('x', table='y')))
4783        '(VAR this: x)'
4784
4785    Args:
4786        name: The name of the var or an expression who's name will become the var.
4787
4788    Returns:
4789        The new variable node.
4790    """
4791    if not name:
4792        raise ValueError("Cannot convert empty name into var.")
4793
4794    if isinstance(name, Expression):
4795        name = name.name
4796    return Var(this=name)
4797
4798
4799def rename_table(old_name: str | Table, new_name: str | Table) -> AlterTable:
4800    """Build ALTER TABLE... RENAME... expression
4801
4802    Args:
4803        old_name: The old name of the table
4804        new_name: The new name of the table
4805
4806    Returns:
4807        Alter table expression
4808    """
4809    old_table = to_table(old_name)
4810    new_table = to_table(new_name)
4811    return AlterTable(
4812        this=old_table,
4813        actions=[
4814            RenameTable(this=new_table),
4815        ],
4816    )
4817
4818
4819def convert(value) -> Expression:
4820    """Convert a python value into an expression object.
4821
4822    Raises an error if a conversion is not possible.
4823
4824    Args:
4825        value (Any): a python object
4826
4827    Returns:
4828        Expression: the equivalent expression object
4829    """
4830    if isinstance(value, Expression):
4831        return value
4832    if value is None:
4833        return NULL
4834    if isinstance(value, bool):
4835        return Boolean(this=value)
4836    if isinstance(value, str):
4837        return Literal.string(value)
4838    if isinstance(value, float) and math.isnan(value):
4839        return NULL
4840    if isinstance(value, numbers.Number):
4841        return Literal.number(value)
4842    if isinstance(value, tuple):
4843        return Tuple(expressions=[convert(v) for v in value])
4844    if isinstance(value, list):
4845        return Array(expressions=[convert(v) for v in value])
4846    if isinstance(value, dict):
4847        return Map(
4848            keys=[convert(k) for k in value],
4849            values=[convert(v) for v in value.values()],
4850        )
4851    if isinstance(value, datetime.datetime):
4852        datetime_literal = Literal.string(
4853            (value if value.tzinfo else value.replace(tzinfo=datetime.timezone.utc)).isoformat()
4854        )
4855        return TimeStrToTime(this=datetime_literal)
4856    if isinstance(value, datetime.date):
4857        date_literal = Literal.string(value.strftime("%Y-%m-%d"))
4858        return DateStrToDate(this=date_literal)
4859    raise ValueError(f"Cannot convert {value}")
4860
4861
4862def replace_children(expression, fun, *args, **kwargs):
4863    """
4864    Replace children of an expression with the result of a lambda fun(child) -> exp.
4865    """
4866    for k, v in expression.args.items():
4867        is_list_arg = isinstance(v, list)
4868
4869        child_nodes = v if is_list_arg else [v]
4870        new_child_nodes = []
4871
4872        for cn in child_nodes:
4873            if isinstance(cn, Expression):
4874                for child_node in ensure_collection(fun(cn, *args, **kwargs)):
4875                    new_child_nodes.append(child_node)
4876                    child_node.parent = expression
4877                    child_node.arg_key = k
4878            else:
4879                new_child_nodes.append(cn)
4880
4881        expression.args[k] = new_child_nodes if is_list_arg else seq_get(new_child_nodes, 0)
4882
4883
4884def column_table_names(expression):
4885    """
4886    Return all table names referenced through columns in an expression.
4887
4888    Example:
4889        >>> import sqlglot
4890        >>> column_table_names(sqlglot.parse_one("a.b AND c.d AND c.e"))
4891        ['c', 'a']
4892
4893    Args:
4894        expression (sqlglot.Expression): expression to find table names
4895
4896    Returns:
4897        list: A list of unique names
4898    """
4899    return list(dict.fromkeys(column.table for column in expression.find_all(Column)))
4900
4901
4902def table_name(table) -> str:
4903    """Get the full name of a table as a string.
4904
4905    Args:
4906        table (exp.Table | str): table expression node or string.
4907
4908    Examples:
4909        >>> from sqlglot import exp, parse_one
4910        >>> table_name(parse_one("select * from a.b.c").find(exp.Table))
4911        'a.b.c'
4912
4913    Returns:
4914        The table name.
4915    """
4916
4917    table = maybe_parse(table, into=Table)
4918
4919    if not table:
4920        raise ValueError(f"Cannot parse {table}")
4921
4922    return ".".join(
4923        part
4924        for part in (
4925            table.text("catalog"),
4926            table.text("db"),
4927            table.name,
4928        )
4929        if part
4930    )
4931
4932
4933def replace_tables(expression, mapping):
4934    """Replace all tables in expression according to the mapping.
4935
4936    Args:
4937        expression (sqlglot.Expression): expression node to be transformed and replaced.
4938        mapping (Dict[str, str]): mapping of table names.
4939
4940    Examples:
4941        >>> from sqlglot import exp, parse_one
4942        >>> replace_tables(parse_one("select * from a.b"), {"a.b": "c"}).sql()
4943        'SELECT * FROM c'
4944
4945    Returns:
4946        The mapped expression.
4947    """
4948
4949    def _replace_tables(node):
4950        if isinstance(node, Table):
4951            new_name = mapping.get(table_name(node))
4952            if new_name:
4953                return to_table(
4954                    new_name,
4955                    **{k: v for k, v in node.args.items() if k not in ("this", "db", "catalog")},
4956                )
4957        return node
4958
4959    return expression.transform(_replace_tables)
4960
4961
4962def replace_placeholders(expression, *args, **kwargs):
4963    """Replace placeholders in an expression.
4964
4965    Args:
4966        expression (sqlglot.Expression): expression node to be transformed and replaced.
4967        args: positional names that will substitute unnamed placeholders in the given order.
4968        kwargs: keyword arguments that will substitute named placeholders.
4969
4970    Examples:
4971        >>> from sqlglot import exp, parse_one
4972        >>> replace_placeholders(
4973        ...     parse_one("select * from :tbl where ? = ?"), "a", "b", tbl="foo"
4974        ... ).sql()
4975        'SELECT * FROM foo WHERE a = b'
4976
4977    Returns:
4978        The mapped expression.
4979    """
4980
4981    def _replace_placeholders(node, args, **kwargs):
4982        if isinstance(node, Placeholder):
4983            if node.name:
4984                new_name = kwargs.get(node.name)
4985                if new_name:
4986                    return to_identifier(new_name)
4987            else:
4988                try:
4989                    return to_identifier(next(args))
4990                except StopIteration:
4991                    pass
4992        return node
4993
4994    return expression.transform(_replace_placeholders, iter(args), **kwargs)
4995
4996
4997def expand(expression: Expression, sources: t.Dict[str, Subqueryable], copy=True) -> Expression:
4998    """Transforms an expression by expanding all referenced sources into subqueries.
4999
5000    Examples:
5001        >>> from sqlglot import parse_one
5002        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y")}).sql()
5003        'SELECT * FROM (SELECT * FROM y) AS z /* source: x */'
5004
5005    Args:
5006        expression: The expression to expand.
5007        sources: A dictionary of name to Subqueryables.
5008        copy: Whether or not to copy the expression during transformation. Defaults to True.
5009
5010    Returns:
5011        The transformed expression.
5012    """
5013
5014    def _expand(node: Expression):
5015        if isinstance(node, Table):
5016            name = table_name(node)
5017            source = sources.get(name)
5018            if source:
5019                subquery = source.subquery(node.alias or name)
5020                subquery.comments = [f"source: {name}"]
5021                return subquery
5022        return node
5023
5024    return expression.transform(_expand, copy=copy)
5025
5026
5027def func(name: str, *args, dialect: DialectType = None, **kwargs) -> Func:
5028    """
5029    Returns a Func expression.
5030
5031    Examples:
5032        >>> func("abs", 5).sql()
5033        'ABS(5)'
5034
5035        >>> func("cast", this=5, to=DataType.build("DOUBLE")).sql()
5036        'CAST(5 AS DOUBLE)'
5037
5038    Args:
5039        name: the name of the function to build.
5040        args: the args used to instantiate the function of interest.
5041        dialect: the source dialect.
5042        kwargs: the kwargs used to instantiate the function of interest.
5043
5044    Note:
5045        The arguments `args` and `kwargs` are mutually exclusive.
5046
5047    Returns:
5048        An instance of the function of interest, or an anonymous function, if `name` doesn't
5049        correspond to an existing `sqlglot.expressions.Func` class.
5050    """
5051    if args and kwargs:
5052        raise ValueError("Can't use both args and kwargs to instantiate a function.")
5053
5054    from sqlglot.dialects.dialect import Dialect
5055
5056    converted = [convert(arg) for arg in args]
5057    kwargs = {key: convert(value) for key, value in kwargs.items()}
5058
5059    parser = Dialect.get_or_raise(dialect)().parser()
5060    from_args_list = parser.FUNCTIONS.get(name.upper())
5061
5062    if from_args_list:
5063        function = from_args_list(converted) if converted else from_args_list.__self__(**kwargs)  # type: ignore
5064    else:
5065        kwargs = kwargs or {"expressions": converted}
5066        function = Anonymous(this=name, **kwargs)
5067
5068    for error_message in function.error_messages(converted):
5069        raise ValueError(error_message)
5070
5071    return function
5072
5073
5074def true():
5075    """
5076    Returns a true Boolean expression.
5077    """
5078    return Boolean(this=True)
5079
5080
5081def false():
5082    """
5083    Returns a false Boolean expression.
5084    """
5085    return Boolean(this=False)
5086
5087
5088def null():
5089    """
5090    Returns a Null expression.
5091    """
5092    return Null()
5093
5094
5095# TODO: deprecate this
5096TRUE = Boolean(this=True)
5097FALSE = Boolean(this=False)
5098NULL = Null()